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