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

  • Thread starter Thread starter Tiamiyu L.O
  • Start date Start date
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
 
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))))))
 
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
Click to expand...

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
 
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
Click to expand...

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
 
Back
Top