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:

APPLE

I want a function that can return me a below value in a next cell with space between:

1 16 16 12 5

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:

=WordCodes(A1)

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:B18A B 3 A 1 4 B 2 5 C 3 etcThe 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.

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 & " " Next 'Strip off last space WordCodes = Left(myCodes, Len(myCodes) - 1) End FunctionPut 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

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

If you

reallywant 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 13Regards

ANTIDISESTABLISHMENTARIANISM Now that's a blast from the past!

What .... you don't use it every day ;-)

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

You're welcome. I had some fun with this solution!

Regards

Humar

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

Assemblywomanwritten by Aristophanes in 392 BC.Who doesn't love a little ancient Greek humor?

λοπαδοτεμαχοσελαχογαλεοκρανιολειψανοδριμυπο

τριμματοσιλφιοκαραβομελιτοκατακεχυμενοκιχλεπι

κοσσυφοφαττοπεριστεραλεκτρυονοπτοκεφαλλιοκιγκλο

πελειολαγῳοσιραιοβαφητραγανοπτερύγων

Hi DerbyDad03, No need to make a meal of it !!!!

Regards

I've found that many chefs use just a bit too much κίγκλο for my taste.

Enough of this --- I'm off to Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch for Christmas.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History