Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting column with text & numbers
I am trying to split a column in a product catalog containing text and
numbers so I can further process the numbers. Each cell is consistent in that the text is on the left side and numbers on the right. The problem is that the quantity of words and numbers may vary from cell to cell. The closest I've come is using space as a divider in "Text to Columns", but this results in more work and concatenating because of the variable cell size. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting column with text & numbers
Here's one way:
A1 = try this out 10 A2 = here's another 1 A3 = again 50.5 A4 = last but not least .59 Enter this formula in B1: =LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) Enter this formula in C1: =SUBSTITUTE(A1,B1&" ","")+0 Select both B1 and C1 then copy down as needed. Biff "craezer" wrote in message ... I am trying to split a column in a product catalog containing text and numbers so I can further process the numbers. Each cell is consistent in that the text is on the left side and numbers on the right. The problem is that the quantity of words and numbers may vary from cell to cell. The closest I've come is using space as a divider in "Text to Columns", but this results in more work and concatenating because of the variable cell size. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting column with text & numbers
Hi,
Suppose your alpha-numeric string is in A1. If you put the following *array* formula in C1, it'll extract just the numbers from A1: =--RIGHT(A1,LEN(A1)-MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),""))+1) Then, you can use the following *standard* formula in B1 to extract just the letters from A1: =LEFT(A1,LEN(A1)-LEN(C1)) An array formula is created via the Ctrl-Shift-Enter key comination, whereas a standard formula is created via the Enter key alone. Cheers -- macropod [MVP - Microsoft Word] "craezer" wrote in message ... | I am trying to split a column in a product catalog containing text and | numbers so I can further process the numbers. Each cell is consistent in that | the text is on the left side and numbers on the right. The problem is that | the quantity of words and numbers may vary from cell to cell. The closest | I've come is using space as a divider in "Text to Columns", but this results | in more work and concatenating because of the variable cell size. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting column with text & numbers
There are probably simpler ways, but here's a possibility, using a user
defined function. Probably not the most elegant, but it should work. Call with: =SplitTextNum(A1,True) to get the text side of things =SplitTextNum(A1,False) to get the number side of things Function SplitTextNum(Part As String, TextPart As Boolean) As String Dim x As Long Dim s As Long Dim i As Long s = Len(Part) x = s For i = 1 To s If "0" <= Mid(Part, i, 1) And Mid(Part, i, 1) <= "9" Then x = i - 1 GoTo Finished End If Next i Finished: If TextPart Then SplitTextNum = Left(Part, x) Else SplitTextNum = Right(Part, s - x) End If End Function Scott craezer wrote: I am trying to split a column in a product catalog containing text and numbers so I can further process the numbers. Each cell is consistent in that the text is on the left side and numbers on the right. The problem is that the quantity of words and numbers may vary from cell to cell. The closest I've come is using space as a divider in "Text to Columns", but this results in more work and concatenating because of the variable cell size. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting column with text & numbers
=LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))-1) Line wrap!!! =LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1) -LEN(SUBSTITUTE(A1," ",""))))-1) When the line wraps at an intentional space in a formula it can be confusing! Biff "T. Valko" wrote in message ... Here's one way: A1 = try this out 10 A2 = here's another 1 A3 = again 50.5 A4 = last but not least .59 Enter this formula in B1: =LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) Enter this formula in C1: =SUBSTITUTE(A1,B1&" ","")+0 Select both B1 and C1 then copy down as needed. Biff "craezer" wrote in message ... I am trying to split a column in a product catalog containing text and numbers so I can further process the numbers. Each cell is consistent in that the text is on the left side and numbers on the right. The problem is that the quantity of words and numbers may vary from cell to cell. The closest I've come is using space as a divider in "Text to Columns", but this results in more work and concatenating because of the variable cell size. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting column with text & numbers
Assuming your data start in A1
B1 =LEFT(A1,MIN(IF(ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIREC T("1:"&LEN(A1)))))-1) ctrl+shift+enter (not just enter) C1 =--RIGHT(A1,LEN(A1)-LEN(B1)) select B1 and C1 and drag down as far as needed "craezer" wrote: I am trying to split a column in a product catalog containing text and numbers so I can further process the numbers. Each cell is consistent in that the text is on the left side and numbers on the right. The problem is that the quantity of words and numbers may vary from cell to cell. The closest I've come is using space as a divider in "Text to Columns", but this results in more work and concatenating because of the variable cell size. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting column with text & numbers
We have a winner! Thank you all so much for your quick response and help.
Teethless Mama's dual column formulas did the trick. "Teethless mama" wrote: Assuming your data start in A1 B1 =LEFT(A1,MIN(IF(ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIREC T("1:"&LEN(A1)))))-1) ctrl+shift+enter (not just enter) C1 =--RIGHT(A1,LEN(A1)-LEN(B1)) select B1 and C1 and drag down as far as needed "craezer" wrote: I am trying to split a column in a product catalog containing text and numbers so I can further process the numbers. Each cell is consistent in that the text is on the left side and numbers on the right. The problem is that the quantity of words and numbers may vary from cell to cell. The closest I've come is using space as a divider in "Text to Columns", but this results in more work and concatenating because of the variable cell size. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting column with text & numbers
TM's first formula picks up the last space character. Change the -1 to -2.
Biff "craezer" wrote in message ... We have a winner! Thank you all so much for your quick response and help. Teethless Mama's dual column formulas did the trick. "Teethless mama" wrote: Assuming your data start in A1 B1 =LEFT(A1,MIN(IF(ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIREC T("1:"&LEN(A1)))))-1) ctrl+shift+enter (not just enter) C1 =--RIGHT(A1,LEN(A1)-LEN(B1)) select B1 and C1 and drag down as far as needed "craezer" wrote: I am trying to split a column in a product catalog containing text and numbers so I can further process the numbers. Each cell is consistent in that the text is on the left side and numbers on the right. The problem is that the quantity of words and numbers may vary from cell to cell. The closest I've come is using space as a divider in "Text to Columns", but this results in more work and concatenating because of the variable cell size. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct issues | Excel Worksheet Functions | |||
How to insert text in one column and populate numbers in another | Excel Worksheet Functions | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
How do I split a column having numbers and text in a random order | Excel Worksheet Functions |