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

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

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

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

--
Jacob


"Tiamiyu L.O" wrote:

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

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

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

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:9 9"))),99)

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

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

On Fri, 15 Jan 2010 09:01:47 -0500, Ron Rosenfeld
wrote:

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

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:9 9"))),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:9 9"))))

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:9 9")))),"-","")

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