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.

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.

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.

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.

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.

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.

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.

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.