Excel function: Text to Numbers

Microsoft Excel 2003 (full product)
December 21, 2009 at 08:44:19
Specs: Windows Vista
Hi, there is a table like A=1 B=2 C=3 D=4 C=5.....
I'm looking for a function(not a VB code, not mac) that can return values of all the alphabetic characters OF ONE CELL based on that table (WITH SPACE BETWEEN THEM).
for example in one cell we have this word:
I want a function that can return me a below value in a next cell with space between:
1 16 16 12 5

See More: Excel function: Text to Numbers

Report •

December 21, 2009 at 09:38:28
Even though you said you didn't want any VB Code, this request is the perfect application for a User Defined Function. A UDF is a Function written in VBA code but used as function within a worksheet.

For example, a UDF named WordCodes() could be written in VBA. You would then simply enter:


to get the result you want.

Here is how APPLE would have to be broken out using "standard" Excel functions:

Let's say APPLE is in A1
Let's say your Letter Code table is in A3:B18

    A   B
3   A   1
4   B   2
5   C   3

The function would be:

=VLOOKUP(MID(A1,1,1),$A$3:$B$18,2,0) & " " & VLOOKUP(MID(A1,2,1),$A$3:$B$18,2,0) & " " &VLOOKUP(MID(A1,3,1),$A$3:$B$18,2,0)& " "&VLOOKUP(MID(A1,4,1),$A$3:$B$18,2,0) & " " & VLOOKUP(MID(A1,5,1),$A$3:$B$18,2,0)

This would look up each letter, one by one, using VLOOKUP and then concatenate the results.

The problem with using this method is that we don't know how long each word will be, so we don't know how many VLOOKUPs to string together. I know that APPLE is 5 letters, so I know I needed 5 VLOOKUPs and 4 spaces.

A VBA based UDF could determine the length of each word and present the same results. If you are interested in seeing how a UDF would be used to meet your requirements, let us know.

Report •

December 21, 2009 at 09:55:42
Just in case you were going to ask...

Try this:

Open the VBA editor Alt-F11
Pull down Insert...Module
Paste this code into the window that opens:

Private Function WordCodes(strWord As String)
'Loop through word
 For nxtChr = 1 To Len(strWord)
'Build result character by character
  myCodes = myCodes & Asc(Mid(strWord, nxtChr, 1)) - 64 & " "
'Strip off last space
  WordCodes = Left(myCodes, Len(myCodes) - 1)
End Function

Put APPLE in A1

In any cell, enter =WordCodes(A1)

PS You don't need the table when using this UDF. The A=1, B=2, etc. is built into the code. This is the line that does it:

Asc(Mid(strWord, nxtChr, 1)) - 64

Asc(A) = 65 so

Asc(A) - 64 = 1

Report •

December 21, 2009 at 12:28:05

As DerbyDad03 says, a User defined function (UDF) written in Visual Basic is the best way to go for this.

If you really want a formula, the following will work on words up to 23 characters long. (This uses the maximum formula length allowed in Excel 2003). You could extend it if you used Excel 2007.
The 'word' is in Cell E2 in this example:
=TRIM(IF(LEN(E2)>0,CODE(MID(E2,1,1))-64,"")&" "&IF(LEN(E2)>1,CODE(MID(E2,2,1))-64,"")&" "&IF(LEN(E2)>2,CODE(MID(E2,3,1))-64,"")&" "&IF(LEN(E2)>3,CODE(MID(E2,4,1))-64,"")&" "&IF(LEN(E2)>4,CODE(MID(E2,5,1))-64,"")&" "&IF(LEN(E2)>5,CODE(MID(E2,6,1))-64,"")&" "&IF(LEN(E2)>6,CODE(MID(E2,7,1))-64,"")&" "&IF(LEN(E2)>7,CODE(MID(E2,8,1))-64,"")&" "&IF(LEN(E2)>8,CODE(MID(E2,9,1))-64,"")&" "&IF(LEN(E2)>9,CODE(MID(E2,10,1))-64,"")&" "&IF(LEN(E2)>10,CODE(MID(E2,11,1))-64,"")&" "&IF(LEN(E2)>11,CODE(MID(E2,12,1))-64,"")&" "&IF(LEN(E2)>12,CODE(MID(E2,13,1))-64,"")&" "&IF(LEN(E2)>13,CODE(MID(E2,14,1))-64,"")&" "&IF(LEN(E2)>14,CODE(MID(E2,15,1))-64,"")&" "&IF(LEN(E2)>15,CODE(MID(E2,16,1))-64,"")&" "&IF(LEN(E2)>16,CODE(MID(E2,17,1))-64,"")&" "&IF(LEN(E2)>17,CODE(MID(E2,18,1))-64,"")&" "&IF(LEN(E2)>18,CODE(MID(E2,19,1))-64,"")&" "&IF(LEN(E2)>19,CODE(MID(E2,20,1))-64,"")&" "&IF(LEN(E2)>20,CODE(MID(E2,21,1))-64,"")&" "&IF(LEN(E2)>21,CODE(MID(E2,22,1))-64,"")&" "&IF(LEN(E2)>22,CODE(MID(E2,23,1))-64,""))

No lookup table is required.

Note that if you tried to use this formula to refer to a cell below row 9 or after column Z, it would fail as it would be too long for Excel 2003.

Although if you named a cell beyond row 9 or column Z using a maximum 2-letter name, you could reference these cells by name and not exceed the maximum formula length.

Also, if you defined constants and sub-formulas with short one- or two-letter names you could squeeze a few more letters in - I managed 28, but its getting a bit difficult to describe at that point !!! (I converted ANTIDISESTABLISHMENTARIANISM) to:
1 14 20 9 4 9 19 5 19 20 1 2 12 9 19 8 13 5 14 20 1 18 9 1 14 9 19 13


Report •

Related Solutions

December 21, 2009 at 13:05:22

Now that's a blast from the past!

Report •

December 21, 2009 at 13:47:46
What .... you don't use it every day ;-)

Report •

December 22, 2009 at 14:39:28
tnx every1 for replying my question...Humar...I have special thank because you helped me to solve this question...your answer was what I exactly looking for..I couldnt figure out myself....ty

Report •

December 22, 2009 at 15:15:40
You're welcome.

I had some fun with this solution!



Report •

December 22, 2009 at 16:07:39
I'm curious...

Which part of Humar's response are you thanking him for?

The 995 character formula or the part where he said "As DerbyDad03 says, a User defined function (UDF) written in Visual Basic is the best way to go for this."


In the friendliest of manners I'd like to point out that my UDF decoded this 171 character word from the ever-popular comedy Assemblywoman written by Aristophanes in 392 BC.

Who doesn't love a little ancient Greek humor?


Report •

December 23, 2009 at 05:29:11
Hi DerbyDad03,

No need to make a meal of it !!!!


Report •

December 23, 2009 at 09:57:19
I've found that many chefs use just a bit too much κίγκλο for my taste.

Report •

December 23, 2009 at 12:52:26
Enough of this ---

I'm off to Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch for Christmas.

Report •

Ask Question