How to seperate ALPHANUMERIC data in a cell in an excel sheet.

[Tiamiyu L.O]

Please Members, I would like somebody to help me on how to enter a formular
to seperate ALPHANUMERIC data in a cell in an excel sheet. For example, if a
cell contain 13050-300-000-000prepaid. What to do to seperate the numeric
from the alpha by entering formular in another cell.

Thank you.

Tiamiyu L.O

 

[Jacob Skaria]

Try the below array formula...Apply using Ctrl+Shift+Enter instead of just
Enter

=LEFT(SUBSTITUTE(A1,"-",),SUM(--(ISNUMBER(VALUE(MID(
SUBSTITUTE(A1,"-",),ROW(1:255),1))))))

 

[Ron Rosenfeld]

On Fri, 15 Jan 2010 02:41:02 -0800, Tiamiyu L.O <Tiamiyu

Please Members, I would like somebody to help me on how to enter a formular
to seperate ALPHANUMERIC data in a cell in an excel sheet. For example, if a
cell contain 13050-300-000-000prepaid. What to do to seperate the numeric
from the alpha by entering formular in another cell.

Thank you.

Tiamiyu L.O

If your "alpha" refers to the word that begins after the final digit in the
string, then you may try this normally entered formula:

=MID(A1,1+LOOKUP(2,1/ISNUMBER(-MID(A1,ROW(INDIRECT("1:99")),1)),ROW(INDIRECT("1:99"))),99)

The "99"'s just need to be some value greater than the length of the longest
string you will be analyzing.
--ron

 

[Ron Rosenfeld]

If your "alpha" refers to the word that begins after the final digit in the
string, then you may try this normally entered formula:

=MID(A1,1+LOOKUP(2,1/ISNUMBER(-MID(A1,ROW(INDIRECT("1:99")),1)),ROW(INDIRECT("1:99"))),99)

The "99"'s just need to be some value greater than the length of the longest
string you will be analyzing.
--ron

The above formula returns the "prepaid" in your string. To return the
previous part, merely use the LEFT function:

=LEFT(A1,LOOKUP(2,1/ISNUMBER(-MID(A1,ROW(INDIRECT("1:99")),1)),ROW(INDIRECT("1:99"))))

Or, if you also want to remove the dashes:

=SUBSTITUTE(LEFT(A1,LOOKUP(2,1/ISNUMBER(-MID(A1,ROW(INDIRECT("1:99")),1)),ROW(INDIRECT("1:99")))),"-","")

If your data is more complex, then post back with some more examples, and what
you want the results to be.
--ron