TEXT STRIPPING

[NH]

Looking for a formula to separate text from numbers
on col A i have values like 10pounds,100meters, 100yards

In short they are values with numbers and then text, with no space between
the two
I need the two separated in cols B and col C

 

[Frank Kabel]

Hi
use the following formulas:
1. Numeric portion:
=--LEFT(A1,SUMPRODUCT(--ISNUMBER(-LEFT(A1,seq))))

2. Text portion:
=MID(A1,SUMPRODUCT(--ISNUMBER(-LEFT(A1,seq))),255)

where seq is a defined name with the formula
=ROW(INDIRECT("1:1024"))

 

[Don Guillett]

I'm not quite clear about what col B and col C should look like when
finished

 

[NH]

Thanks frank I works

Hi
use the following formulas:
1. Numeric portion:
=--LEFT(A1,SUMPRODUCT(--ISNUMBER(-LEFT(A1,seq))))

2. Text portion:
=MID(A1,SUMPRODUCT(--ISNUMBER(-LEFT(A1,seq))),255)

where seq is a defined name with the formula
=ROW(INDIRECT("1:1024"))

 

[GerryK]

I'd like to use this but I'm getting a 0 in front of the
text portion.
How can I eliminate that!
TIA

 

[Frank Kabel]

Hi
what is the exact formula you have used and what is your testdata.
Maybe change the second formula to:
=MID(A1,SUMPRODUCT(--ISNUMBER(-LEFT(A1,seq)))+1,255)

 

[GerryK]

Hi Frank,
The +1 at the end works, Thank you!
I was trying to separate a long column list
of "addressstreet" such as 1234Anywhere.

GK