text word to numbers

[jammoca]

I have a very long and cumbersome formula that converts a word into a number value.

It takes each letter in the word and assigns a number value in its place (based on where that letter is in the alphabet).

eg: the word "Smith" would be replaced by "19139208" as 'S'is the 19th letter in the alphabet, 'M' is the 13th letter and so on etc etc.

I'd like to know if there is a much more user-friendly way to do this ?

 

[GlennUK]

Can you explain why you want to do this? What formula are you using? I'd imagine that something using the CODE function ( less 64 ) would be involved.

 

[Andrew Poulsom]

The closest I can get is:

=SUMPRODUCT(--((CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))-64)&REPT("00",(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))))))

which returns 1913092008, ie each number is in the format 00.