unique ID from a name

[stuck]

I would like a formula to convert a list of names in cells into a code number based on the first character of each part of the name and the number of characters in that part of the name, e.g.
Ann Other would become A3O5
Ann Other One would become A305O3
the word delimiter will always be a space but the name could have any number of parts, though I think a Spanish style in four parts (double Christian name, double surname) is likely to be the most complex.

I can get the first part with:
=CONCATENATE(LEFT(A1,1),(FIND(" ",A1)-1))
but already I'm thinking that's is probably not the way best way to start...

Ken

[HansV]

I think you'll need VBA for this. Copy the following function into a module:

Code: Select all

Function Name2Code(varName) As String
  Dim arrParts
  Dim i As Integer
  Application.Volatile
  arrParts = Split(varName)
  For i = LBound(arrParts) To UBound(arrParts)
    Name2Code = Name2Code & Left(arrParts(i), 1) & Len(arrParts(i))
  Next i
End Function

Use like this in a formula:

=Name2Code(A1)

If you store the function in your personal macro workbook Personal.xls (Excel 2003 or before) or Personal.xlsb (Excel 2007 or later), use

=Personal.xls!Name2Code(A1)

or

=Personal.xlsb!Name2Code(A1)

depending on your version of Excel.

[stuck]

That was quick and it does the job very nicely.


Ken

[ChrisGreaves]

I would like a formula to convert a list of names in cells into a code number based on the first character of each part of the name and the number of characters in that part of the name,

The airline reservation systems have bene using this for 40+ years.
For the life of me I can't think of the name of the scheme; it'll come to me withing 30 minutes of hitting "Submit" and about three minutes after another lounger jogs my memory.
I know it was in full swing in 1973, for three of my team members were surprised at BOAC(?) offices in London by asking if their reservations were confirmed. "Name?"; "Witt", says one of the three, not spelling it out. "Witt, Malthus and Steketee confirmed" shot back the clerk in seconds, flooring all three of them.

[HansV]

Do you mean Soundex?

[HansV]

it does the job very nicely.

And it's a smart function too:

x53.png

[Leif]

And it's a smart function too:

Very clever!

[HansV]

Or for the Star Wars fans:

x54.png

[ChrisGreaves]

Do you mean Soundex?

I knew it ...!
P.S. There's a FAQ in case you get the wrong name!