How do you convert a name eg ABCDE to a number eg 12345.?
where A=1,B=2,C=3 etc..
I need to input the name in 1 cell and see the converted number in adjacent cell,
So I know there will be some sort of lookup but I can not figure out how to lookup each letter.
Thanks
hope this makes sense.
Easiest to do with VBA.
There is no way that I know of to easily concatenate an array of values without VBA. Oh, you could do a long involved functions, consisting of something like:
=IFERROR(CODE(MID(A1,1,1))-64,"") &
IFERROR(CODE(MID(A1,2,1))-64,"") &
IFERROR(CODE(MID(A1,3,1))-64,"") ...
You would have to extend the number of elements until you had one element for every letter in the longest name you might use.
However, since you might have other characters in a name besides [A-Z], you would have to test for the character, and decide what you want to do if it is a <space>, <dot>, <comma>, etc.
Assuming that all the letters were already capitalized, you might be able to get away with something like:
=IFERROR(IF(AND(MID(A1,1,1)>="A",MID(A1,1,1)<="Z"),CODE(MID(A1,1,1))-64,MID(A1,1,1)),"") &
IFERROR(IF(AND(MID(A1,2,1)>="A",MID(A1,2,1)<="Z"),CODE(MID(A1,2,1))-64,MID(A1,2,1)),"") &
IFERROR(IF(AND(MID(A1,3,1)>="A",MID(A1,3,1)<="Z"),CODE(MID(A1,3,1))-64,MID(A1,3,1)),"") ...
Again extending the formula to account for the possible number of characters in the name.
It gets even more complicated when you try to decide how you want to handle the first nine letters since they will be represented by a single digit in your scheme, and the rest of the alphabet by two digits.
Or, more simply, a User Defined Function using VBA:
To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this User Defined Function (UDF), enter a formula like
=StringToNums(A1)
in some cell.
I assume that each letter would be represented by two digits, otherwise there would be no difference between "AA" and "K", but you could easily change the scheme.
And I also assumed that if a character in the name was not a letter, it would be returned as itself; again, something easily changed.
=======================================
Option Explicit
Function StringToNums(s As String) As String
Dim i As Long
Dim S1 As String, S2 As String, sTemp As String
S1 = UCase(s)
For i = 1 To Len(S1)
S2 = Mid(S1, i, 1)
Select Case S2
Case "A" To "Z"
sTemp = sTemp & Format(Asc(S2) - 64, "00")
Case Else
sTemp = sTemp & S2
End Select
Next i
StringToNums = sTemp
End Function
==============================
