How to separate numbers from text??

[gmoexcel]

Here are my sample cells:


A B C D
ABA 123456789SMITH
ABA
ABA
ABA
ABA

I want to go from the example at the top to the example
at the bottom. Is there a function or formula that will allow
me to separate numbers from text?

A B C D
ABA 123456789 SMITH
ABA
ABA
ABA
ABA

 

[Guest]

It looks like there is if you have the same number of numbers and letters in
each one you're trying to split. In the 'test' category of formulas, there
are two functions called 'left' and 'right'. You could use "left" to split
the first set of numbers and the "right" function in the other cell for the
last set of letters. I hope this helps.

I posted the question about the formula gliche right before yours. Is your
copy formula function working properly?

 

[Bernard Liengme]

When you show just one example lots of questions are unanswered.
Do you always have 9 digits before the text?
Is the ABA part of the entry or is 123456789SMITH in a cell on its own?
Have you experimented with Data | Text to Columns ?
come back and we will try to help

 

[gmoexcel]

Yes there is always 9 digits on the left of the text, that's why I went
from 1 to 9 as in a SSN. Never mind the ABA, has nothing to do with
the question. 123456789SMITH is a cell on its own, that is the one I
am attempting to separate.

What is Data | Text to Columns ?

This is starting to get very complicated.

gmo

 

[Dave O]

Not to be contrary, but since there are *always* 9 digits on the left,
it becomes much simpler. With one of your entries in cell A1, try this
in cell B1:
=LEFT(A1,9)
.... and this in cell C1:
=MID(A1,10,LEN(A1))

Or, to get trickier and put the SSN into typical format, try this in
cell B1:
=MID(A1,1,3)&"-"&MID(A1,4,2)&"-"&MID(A1,6,4)
.... and the earlier mentioned formula in C1.

 

[gmoexcel]

Not to be contrary, but since there are *always* 9 digits on the left,
it becomes much simpler. With one of your entries in cell A1, try
this
in cell B1:
=LEFT(A1,9)
.... and this in cell C1:
=MID(A1,10,LEN(A1))

Or, to get trickier and put the SSN into typical format, try this in
cell B1:
=MID(A1,1,3)&"-"&MID(A1,4,2)&"-"&MID(A1,6,4)
.... and the earlier mentioned formula in C1.

This really helped. You saved us many hours of work. In the future I
will try to be more detailed. It would help if there was a way to
paste spreadsheets on this forum.

 

[Dave O]

Glad this was helpful to you! The LEFT and MID functions in Excel are
just two examples of the many functions that parse data- it may be
useful for you and your team to learn about these functions if you
anticipate similar work in the future.

 

[gmoexcel]

Glad this was helpful to you! The LEFT and MID functions in Excel are
just two examples of the many functions that parse data- it may be
useful for you and your team to learn about these functions if you
anticipate similar work in the future.

Actually I have tried to use the RIGHT function to parse a column of
numbers but I keep getting a #VALUE error.

The data was imported from a report and was originally formatted like
this: 1.22005E+16. In Excel we reformatted it to a number, now it
looks like this: 12200500001044000. Which is actually a combination
of date an dollar amount.

What I am intenting to do is to add the last 7 digits from the right
and get my totals that way, then get rid of the first 10 digits. I
have tried several different functions but I always get the #VALUE
error. Any help will be greatly appreciated.

 

[Dave O]

Hi, sorry for the delayed response! I didn't notice you'd posted
again.

In your example, 12200500001044000, the first 6 columns are the date
stamp, presumably 12/20/2005. But the string itself is 17 characters
long, so just to be all inclusive you don't want those 7 characters
(altho you do for this example), but instead the rightmost 11
characters:
=RIGHT(A1,11)
This returns a text string, which will not provide any useable
information to a math formula. So nest in the VALUE formula to convert
the text to a value:
=VALUE(RIGHT(A1,11))

 

[Dave O]

And totally by the way, you can convert the datestamp (assuming it
means (in this example) 12/20/2005) to an Excel readable date with this
formula:
=DATEVALUE(MID(A1,1,2)&"/"&MID(A1,3,2)&"/"&MID(A1,5,2))
.... and format the results as a date.