How to separate text and numbers in one cell or column?
Hi Jacob,
Thanks....this helps.
Thanks to both Jacob and T.Valko...you helped me fix an issue I have been
struggling with for years. Now I don;t have to go to UNIX and stay in
excel...:-)
Thanks again.
--
novastar
"Jacob Skaria" wrote:
In B1
=SUBSTITUTE(A1,C1,)
In C1
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))
If this post helps click Yes
---------------
Jacob Skaria
"novastar" wrote:
Hi T.Valco,
Thanks........this helps me get the numbers in a column..
Now....I would like to eliminate the numbers and extract only the text in a
different column...how do i do this?
--
novastar
"T. Valko" wrote:
This will extract the last "word" in a cell:
=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255))
Note that the result is a *TEXT* string even if the last "word" in the cell
is a number. If your numbers can contain leading 0s then you'll need to
extract them as text in order to retain the leading 0s.
--
Biff
Microsoft Excel MVP
"novastar" wrote in message
...
Hi,
I have a column of cells which are in any of the following formats:
x #
x x #
x x x #
where x could be any number of characters (a-z or A-Z) either upper or
lower
case.
# could be a 1,2 or 3 digit number 0-9
I generally have to post process this file in UNIX before I import it into
excel and do some modifications.
Is there an easier way to do this in Excel? I don't mind copying the
column
and post processing one column to retain numbers and another column to
retain
text.
--
novastar
|