Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
I trying to write a formula where if I input any letter into a cell I want
the adjacent cell to display the alphebtic number of that letter. Where A=1 B=2 etc etc Z=26 Example. I put letter d in cell A2 I then want cell B2 to display 4 I put letter z in cell A2 I then want cell B2 to display 26 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
Try this in B2, where A2 contains the letter:
=CHAR(UPPER(A2))-64 Hope this helps. Pete |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
Try:
=IF(A8="","",IF(OR(CODE(UPPER(A8))<65,CODE(UPPER(A 8))91),"Not a Letter",CODE(UPPER(A8))-64)) -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "2pojeff" wrote in message ... I trying to write a formula where if I input any letter into a cell I want the adjacent cell to display the alphebtic number of that letter. Where A=1 B=2 etc etc Z=26 Example. I put letter d in cell A2 I then want cell B2 to display 4 I put letter z in cell A2 I then want cell B2 to display 26 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
Nope this did not work
"Pete_UK" wrote: Try this in B2, where A2 contains the letter: =CHAR(UPPER(A2))-64 Hope this helps. Pete |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
Hello Sandy
I typed this formula into cell B9 and entered a letter in cell A8 this did not work. "Sandy Mann" wrote: Try: =IF(A8="","",IF(OR(CODE(UPPER(A8))<65,CODE(UPPER(A 8))91),"Not a Letter",CODE(UPPER(A8))-64)) -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "2pojeff" wrote in message ... I trying to write a formula where if I input any letter into a cell I want the adjacent cell to display the alphebtic number of that letter. Where A=1 B=2 etc etc Z=26 Example. I put letter d in cell A2 I then want cell B2 to display 4 I put letter z in cell A2 I then want cell B2 to display 26 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
I'd try it again, but use =code()
=CODE(UPPER(A2))-64 2pojeff wrote: Nope this did not work "Pete_UK" wrote: Try this in B2, where A2 contains the letter: =CHAR(UPPER(A2))-64 Hope this helps. Pete -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
Hi
Why don't you create a VLOOKUP. eg. Put letters A....D in cells A1 to A26 and numbers in B1 to B26 then use =VLOOKUP(Cell, A1:B26,2, False) to find the number that corresponds to the letter. The table can even be put on a different sheet. HTH Michael "2pojeff" wrote: Hello Sandy I typed this formula into cell B9 and entered a letter in cell A8 this did not work. "Sandy Mann" wrote: Try: =IF(A8="","",IF(OR(CODE(UPPER(A8))<65,CODE(UPPER(A 8))91),"Not a Letter",CODE(UPPER(A8))-64)) -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "2pojeff" wrote in message ... I trying to write a formula where if I input any letter into a cell I want the adjacent cell to display the alphebtic number of that letter. Where A=1 B=2 etc etc Z=26 Example. I put letter d in cell A2 I then want cell B2 to display 4 I put letter z in cell A2 I then want cell B2 to display 26 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
What were the results?
What is "did not work"? Sandy's formula and the shorter one from Dave P. both work for me. Gord Dibben MS Excel MVP On Thu, 20 Apr 2006 15:36:02 -0700, 2pojeff wrote: Hello Sandy I typed this formula into cell B9 and entered a letter in cell A8 this did not work. "Sandy Mann" wrote: Try: =IF(A8="","",IF(OR(CODE(UPPER(A8))<65,CODE(UPPER(A 8))91),"Not a Letter",CODE(UPPER(A8))-64)) -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "2pojeff" wrote in message ... I trying to write a formula where if I input any letter into a cell I want the adjacent cell to display the alphebtic number of that letter. Where A=1 B=2 etc etc Z=26 Example. I put letter d in cell A2 I then want cell B2 to display 4 I put letter z in cell A2 I then want cell B2 to display 26 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
2pojeff" wrote in message
... Hello Sandy I typed this formula into cell B9 and entered a letter in cell A8 this did not work. It works for me - although I would think that it would make more sense if you typed it into B8 not B9 but it will work in any cell. -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk " |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
Thanks for the correction, Dave - I rushed it!
Pete |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
Dont'cha just love trying to help with a bit of input, and then getting a nil
response whatsoever from the OP. We all have a diffrent spin on a question, but it would be nice to know which way the user goes. Why do we bother !!! Michael M. "Sandy Mann" wrote: 2pojeff" wrote in message ... Hello Sandy I typed this formula into cell B9 and entered a letter in cell A8 this did not work. It works for me - although I would think that it would make more sense if you typed it into B8 not B9 but it will work in any cell. -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk " |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
<<<"Why do we bother !!!"
Because we enjoy doing it ... with or without any appreciation! And we realize that many posters are inexperienced with using these NGs, and therefore are very easily confused and unable to even find their own OPs, much less carry on any extended conversation with other responders. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Michael M" wrote in message ... Dont'cha just love trying to help with a bit of input, and then getting a nil response whatsoever from the OP. We all have a diffrent spin on a question, but it would be nice to know which way the user goes. Why do we bother !!! Michael M. "Sandy Mann" wrote: 2pojeff" wrote in message ... Hello Sandy I typed this formula into cell B9 and entered a letter in cell A8 this did not work. It works for me - although I would think that it would make more sense if you typed it into B8 not B9 but it will work in any cell. -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk " |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
Hi Ragdyer,
I'm still struggling with some of the acronyms. My kids are always ribbing me about it! OP=? Ken Johnson |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
Ken Johnson Wrote: Hi Ragdyer, I'm still struggling with some of the acronyms. My kids are always ribbing me about it! OP=? Ken JohnsonHi Ken, I believe "OP" = "Original Post" -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=534791 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
Hi Desert Piranha,
Thanks for that, it makes more sense then "Other Person" or "Operational Problem":-) Ken Johnson |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
Hi RD
You're absolutely right, of course. I guess the good old days of thanks are long gone....I must be getting old. But I dooo enjoy doing it all the same Michael M "Ragdyer" wrote: <<<"Why do we bother !!!" Because we enjoy doing it ... with or without any appreciation! And we realize that many posters are inexperienced with using these NGs, and therefore are very easily confused and unable to even find their own OPs, much less carry on any extended conversation with other responders. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Michael M" wrote in message ... Dont'cha just love trying to help with a bit of input, and then getting a nil response whatsoever from the OP. We all have a diffrent spin on a question, but it would be nice to know which way the user goes. Why do we bother !!! Michael M. "Sandy Mann" wrote: 2pojeff" wrote in message ... Hello Sandy I typed this formula into cell B9 and entered a letter in cell A8 this did not work. It works for me - although I would think that it would make more sense if you typed it into B8 not B9 but it will work in any cell. -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk " |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
Depending on context, it refers to either:
Original PostER OR Original Post -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Ken Johnson" wrote in message ups.com... Hi Desert Piranha, Thanks for that, it makes more sense then "Other Person" or "Operational Problem":-) Ken Johnson |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
Ken
You can search for usenet acronyms here........... http://silmaril.ie/cgi-bin/uncgi/acronyms Gord Dibben MS Excel MVP On 23 Apr 2006 19:12:36 -0700, "Ken Johnson" wrote: Hi Ragdyer, I'm still struggling with some of the acronyms. My kids are always ribbing me about it! OP=? Ken Johnson |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Letters and Number formulas
Hi Gord and RagDyer,
Thanks for that. My kids will not be so amused now. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel, how do I change the column headings from letters to number | Excel Discussion (Misc queries) | |||
how i write a number on a column A and see that number on letters. | Excel Worksheet Functions | |||
Counting the number of letters in a cell | Excel Discussion (Misc queries) | |||
How do you sort words in Excel by the number of letters in a word | New Users to Excel | |||
Calculating number of letters in a string ... | Excel Worksheet Functions |