I am trying to split up a cell into numbers and charachters and place them in
separate columns, but the lenght of the number part varies as does the
content of the character part.

For example, one cell could be 5#, 10Tins, 4 lb, 100Pcs, etc.
I would like to be able to pull out the #, Tins, lb, and Pcs in to their
own column.

I have found this formula:
=LEFT(A1,FIND("-",A1,1)-1)

but it assumes some level of consistency, the "-" in the cell.

Any ideas appreciated.

Robert

Perhaps something like this:

For a value in A1

The numeric (left) part:
B1:
=--LEFT(A1,MATCH(FALSE,ISNUMBER(--MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)),0)-1)

The unit of measure (right) part:
C1:
=TRIM(MID(A1,MATCH(FALSE,ISNUMBER(--MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)),0),255))

Note: Commit those array formulas by holding down the [Ctrl][Shift] keys
when you press [Enter].

Copy those formulas down as far as you need.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"frosterrj" wrote:

I am trying to split up a cell into numbers and charachters and place them in
separate columns, but the lenght of the number part varies as does the
content of the character part.

For example, one cell could be 5#, 10Tins, 4 lb, 100Pcs, etc.
I would like to be able to pull out the #, Tins, lb, and Pcs in to their
own column.

I have found this formula:
=LEFT(A1,FIND("-",A1,1)-1)

but it assumes some level of consistency, the "-" in the cell.

Any ideas appreciated.

Robert

It's a bit ugly, but it seems to work. Assuming your values start in
A1 and go down, the following formula in cell B1 will break out the
number.

=IF(ISERR(VALUE(LEFT(A1,2))),VALUE(LEFT(A1,1)),IF( ISERR(VALUE(LEFT(A1,3))),VALUE(LEFT(A1,2)),IF(ISER R(VALUE(LEFT(A1,4))),VALUE(LEFT(A1,3)),IF(ISERR(VA LUE(LEFT(A1,5))),VALUE(LEFT(A1,4)),"Check
num length"))))

This will test the first two characters, then the first 3, then the
first 4 etc. to see if it is a number, and when it hits a length that
includes an alpha, it will drop down a character and return the value.
I stopped at 4 digits, but obviously you can extend it as much as you
need.

Then you can use the following formula in cell C1 to give you the text.

=RIGHT(A1,LEN(A1)-LEN(B1))

Note: The second formula will include a space if there is one. You
may want to remove all spaces before you begin to avoid that.

Hope that helps...