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

T

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
 
J

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

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top