Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns Letters and Numbers
How do I split a cell with numbers and letters into 2 separate cells. I have
tried the text to columns wizard and can't figure it out. Here is an example of what I am trying to split. There are no spaces between the numbers and letters. A1: 9A A2: 9B A3: 19A A4: 19C |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns Letters and Numbers
You have to add two auxilary columns with this formula in each column. The
copy formula down new columns. These formulkas only work when you have a fixed number of characters (in this case one character) at the end of the string. =LEFT(A1,LEN(A1)-1) =RIGHT(A1,1) "Dot" wrote: How do I split a cell with numbers and letters into 2 separate cells. I have tried the text to columns wizard and can't figure it out. Here is an example of what I am trying to split. There are no spaces between the numbers and letters. A1: 9A A2: 9B A3: 19A A4: 19C |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns Letters and Numbers
B1:
=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))) C1: =SUBSTITUTE(A1,B1,"") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dot" wrote in message ... How do I split a cell with numbers and letters into 2 separate cells. I have tried the text to columns wizard and can't figure it out. Here is an example of what I am trying to split. There are no spaces between the numbers and letters. A1: 9A A2: 9B A3: 19A A4: 19C |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns Letters and Numbers
On Sat, 26 Jul 2008 04:31:00 -0700, Dot wrote:
How do I split a cell with numbers and letters into 2 separate cells. I have tried the text to columns wizard and can't figure it out. Here is an example of what I am trying to split. There are no spaces between the numbers and letters. A1: 9A A2: 9B A3: 19A A4: 19C You don't write exactly what you want. Do you want to have the numbers in one cell and the letter in the other? If so, then try these formulas: B1: =LOOKUP(9.9E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&" 0123456789")),ROW(INDIRECT("1:"&LEN(A1))))) C1: =SUBSTITUTE(A1,B1,"",1) and fill down as far as necessary. If you then want to eliminate the formulas and column A, you could select B1:Cn Edit/Copy Edit/Paste Special/Values Then delete column A --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns Letters and Numbers
Thanks Joel.. Simple and it works perfectly.
"Joel" wrote: You have to add two auxilary columns with this formula in each column. The copy formula down new columns. These formulkas only work when you have a fixed number of characters (in this case one character) at the end of the string. =LEFT(A1,LEN(A1)-1) =RIGHT(A1,1) "Dot" wrote: How do I split a cell with numbers and letters into 2 separate cells. I have tried the text to columns wizard and can't figure it out. Here is an example of what I am trying to split. There are no spaces between the numbers and letters. A1: 9A A2: 9B A3: 19A A4: 19C |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns Letters and Numbers
Attn: BOB PHILLIPS:
How did you ever have the wherewithal to come up with your formula: =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))) which separates the "number" portion of the data (such as 'splitting' 19a into 19?????? Please advise! I am fully aware that it works perfectly, but without your input, I NEVER could have done this on my own!! Thanks, FLKulchar "Bob Phillips" wrote in message ... B1: =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))) C1: =SUBSTITUTE(A1,B1,"") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dot" wrote in message ... How do I split a cell with numbers and letters into 2 separate cells. I have tried the text to columns wizard and can't figure it out. Here is an example of what I am trying to split. There are no spaces between the numbers and letters. A1: 9A A2: 9B A3: 19A A4: 19C |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Columns are numbers (instead of letters). How to revert? XLS | Excel Worksheet Functions | |||
columns appear as numbers not letters | Excel Discussion (Misc queries) | |||
I have numbers instead of letters on my columns, why? | Excel Discussion (Misc queries) | |||
Help, my columns now have numbers instead of letters. | Excel Discussion (Misc queries) | |||
Columns in letters vs numbers | Excel Discussion (Misc queries) |