Converting a text string to numbers...

[wbbane]

Hello there, I am wondering if someone can help me. I have a list of text strings that I'd like to convert to a list of numbers, such that each letter equates to:

A = 10
B = 11
C = 12, etc.

At this point I'm using the MID function to pull specific letters out of a text string, and then the CODE function to convert them to a number.

So =CODE(MID(A1,1,1))-87 = 10, where A1 = "ADE"

So far so good, but here's my issue: I don't know how to tell Excel that the number of characters varies in the text string I want to convert.

Optimally, I'd like to convert ADE to 101415, then add on six zeroes to the end of that number to allow for the fact that other text strings may have up to a total of six letters.

I'm doing all this so I can rank a list of random text strings alphabetically. I want to take the number assigned to each text string, then I can use RANK to order those strings (I think!).

Anyway, thank you so much if anyone can help.

Take care,

--Bill

 

[mikerickson]

I think that this will convert the string in A1 to the number you want

=SUMPRODUCT(CODE(MID(A1,ROW(INDIRECT("1:6")),1)&"7")-55,10^(2*(6-ROW(INDIRECT("1:6")))))

If you use the custom number format 00 00 00 00 00 00 , the result will look like this:
ADE >> 10 13 14 00 00 00

 

[MickG]

Hi, Try this UDFunction.
Enter as :- =TxNo(A1), Where "A1" Holds your String.

Function TxNo(rng [COLOR="Navy"]As[/COLOR] Range) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] L [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] N [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]For[/COLOR] N = 1 To Len(rng)
    L = Mid(rng, N, 1)
        [COLOR="Navy"]For[/COLOR] Num = 65 To 90
            [COLOR="Navy"]If[/COLOR] Chr(Num) = UCase(L) [COLOR="Navy"]Then[/COLOR]
                Txt = Txt & Num - 55
                [COLOR="Navy"]Exit[/COLOR] For
            [COLOR="Navy"]End[/COLOR] If
         [COLOR="Navy"]Next[/COLOR] Num
 [COLOR="Navy"]Next[/COLOR] N
TxNo = Txt
[COLOR="Navy"]End[/COLOR] Function

Regards Mick

 

[wbbane]

mikerickson and MickG, thanks so much for your help. mikerickson, your formula is awesome, I am pulling it apart now to understand the intuition behind it, another user was kind enough to explain its premise. ...MickG, thank you very much for the UDF code, I'm in the process of figuring out how to implement it now, I have a lot to learn RE: code.

Again, thank you so much for your help, I appreciate it.

 

[barry houdini]

I'm doing all this so I can rank a list of random text strings alphabetically.

Hello Bill,

You could probably do that directly, without converting into a number, e.g. if text strings are in A1:A100 try this formula in B1 copied down

=COUNTIF(A$1:A$100,"<"&A$1:A$100)+1

 

[peggymich]

Hello Bill,

You could probably do that directly, without converting into a number, e.g. if text strings are in A1:A100 try this formula in B1 copied down

=COUNTIF(A$1:A$100,"<"&A$1:A$100)+1

That is truly AWESOME. When I did my search for this I thot there was very little chance of findin a solution - never mind so simple!

Thanks SO much for botherin to post