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.
|