Home 
Search 
Today's Posts 
#1




convert cells containing alpha to numeric
In EXCEL XP PRO I am trying to write a formula to convert cells containing a
letter to a number and am having little success. i.e. A1005 to 11005 or B1273 to 21273. Can you enlighten me? 
#2




Always a single letter and at the start?
=RIGHT(A1,LEN(A1)) or select all data and do Data / Text to Columns Fixed Width / Set the break after the first character.  Regards Ken....................... Microsoft MVP  Excel Sys Spec  Win XP Pro / XL 97/00/02/03  It's easier to beg forgiveness than ask permission :)  "ezu" wrote in message ... In EXCEL XP PRO I am trying to write a formula to convert cells containing a letter to a number and am having little success. i.e. A1005 to 11005 or B1273 to 21273. Can you enlighten me? 
#3




Hi
There isn't enough info for exact answer. Start the source string always with letter, or can it be simply numeric too. Can there be 2 or 3 or ... characters at start. Etc. For case the leftmost character will always be a letter, and rest of string characters are numbers, the next formulas will do (with string in cell A2) =RIGHT(A2,LEN(A2)1) or MID(A2,2,99) (you can replace the number 99 in second formula with any big enough  it must be at least as much as the length of longest string) Arvi Laanemets "ezu" wrote in message ... In EXCEL XP PRO I am trying to write a formula to convert cells containing a letter to a number and am having little success. i.e. A1005 to 11005 or B1273 to 21273. Can you enlighten me? 
#4




Hi,
Assuming that the data is in cell A1, array enter (Ctrl+Shift+Enter) the following formula in cell B1. =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$5), 1)),0),COUNT(1*MID(A1,ROW($1:$15),1))) You should get only the numeric portion irrespective of the number of text characters inthe beginning. Regards, Ashish Mathur "ezu" wrote: In EXCEL XP PRO I am trying to write a formula to convert cells containing a letter to a number and am having little success. i.e. A1005 to 11005 or B1273 to 21273. Can you enlighten me? 
#5




Slightly shorter version
=MID(A1,MATCH(1,ISNUMBER(MID(A1,ROW(INDIRECT("1:15")),1)),0),LEN(A1))  Regards Ken....................... Microsoft MVP  Excel Sys Spec  Win XP Pro / XL 97/00/02/03  It's easier to beg forgiveness than ask permission :)  "Ashish Mathur" wrote in message ... Hi, Assuming that the data is in cell A1, array enter (Ctrl+Shift+Enter) the following formula in cell B1. =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$5), 1)),0),COUNT(1*MID(A1,ROW( $1:$15),1))) You should get only the numeric portion irrespective of the number of text characters inthe beginning. Regards, Ashish Mathur "ezu" wrote: In EXCEL XP PRO I am trying to write a formula to convert cells containing a letter to a number and am having little success. i.e. A1005 to 11005 or B1273 to 21273. Can you enlighten me? 
#6




Following your scenario, that you want A = 1 and B = 2,
I assume you want Z = 26. If that's the case, I'd guess that the easiest way would be to create a list, in an out of the way area of your sheet, and have a formula refer to the list to find the exact value for the first letter in the cell, and then append the numeric portion to that value. In Z1 to Z26, enter the letters of the alphabet. Then, assuming your data list started in A1, Enter this formula in B1 and drag down to copy as needed: =(MATCH(LEFT(A1),$Y$1:$Y$26)&RIGHT(A1,LEN(A1)1))  HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "ezu" wrote in message ... In EXCEL XP PRO I am trying to write a formula to convert cells containing a letter to a number and am having little success. i.e. A1005 to 11005 or B1273 to 21273. Can you enlighten me? 
#7




Sorry, wrong column !
Use this: =(MATCH(LEFT(A1),$Z$1:$Z$26)&RIGHT(A1,LEN(A1)1)) HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "RagDyeR" wrote in message ... Following your scenario, that you want A = 1 and B = 2, I assume you want Z = 26. If that's the case, I'd guess that the easiest way would be to create a list, in an out of the way area of your sheet, and have a formula refer to the list to find the exact value for the first letter in the cell, and then append the numeric portion to that value. In Z1 to Z26, enter the letters of the alphabet. Then, assuming your data list started in A1, Enter this formula in B1 and drag down to copy as needed: =(MATCH(LEFT(A1),$Y$1:$Y$26)&RIGHT(A1,LEN(A1)1))  HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "ezu" wrote in message ... In EXCEL XP PRO I am trying to write a formula to convert cells containing a letter to a number and am having little success. i.e. A1005 to 11005 or B1273 to 21273. Can you enlighten me? 
#8




"Ken Wright" wrote...
Always a single letter and at the start? =RIGHT(A1,LEN(A1)) .... ?! How does this differ from =LEFT(A1,LEN(A1)) or just =A1 ? 
#9




"ezu" wrote...
In EXCEL XP PRO I am trying to write a formula to convert cells containing a letter to a number and am having little success. i.e. A1005 to 11005 or B1273 to 21273. If only the initial character would be nonnumeric, and only AI, they you could try =((CODE(UPPER(LEFT(x,1))64)&MID(x,2,1024)) 
#10




Neat !
Have to remember that one.  Regards, RD  Please keep all correspondence within the NewsGroup, so all may benefit !  "Harlan Grove" wrote in message ... "ezu" wrote... In EXCEL XP PRO I am trying to write a formula to convert cells containing a letter to a number and am having little success. i.e. A1005 to 11005 or B1273 to 21273. If only the initial character would be nonnumeric, and only AI, they you could try =((CODE(UPPER(LEFT(x,1))64)&MID(x,2,1024)) 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Convert data type of cells to Text,Number,Date and Time  Excel Worksheet Functions  
To safety merge cells without data destroyed, and smart unmerge!  Excel Discussion (Misc queries)  
Heps to design Locked/Unlocked cells in protected worksheet  Excel Discussion (Misc queries)  
Convert data of cells to any type: Number, Date&Time, Text  Excel Discussion (Misc queries)  
Convert Numeric into Text  Excel Worksheet Functions 