Remove Numbers from text
 

I have a long column with numbers on the leftside of column then a space
then text. How do I remove the numbers to an adjacent column eg below

A1 B1
10 text xxxxx
111 textxxxx
112 textxxx etc
Would like:
A1 B1
10 textxxxxx
111 textxxxx
112 etc

regards Dave.

Hi!

Try this in A1 and copy down as needed:

=--LEFT(B1,FIND(" ",B1)-1)

Biff

"David" wrote in message
...
I have a long column with numbers on the leftside of column then a space
then text. How do I remove the numbers to an adjacent column eg below

A1 B1
10 text xxxxx
111 textxxxx
112 textxxx etc
Would like:
A1 B1
10 textxxxxx
111 textxxxx
112 etc

regards Dave.

Biff, Thankyou for your formula, the only thing is that it does NOT remove
the digits from the main cell (B1 etc), only copies them. So if I use a
LEFT or MID etc, then manually wipe out the digits left in the B column then
the results collapse in the A column. I want if possible to once and for all
remove digits(or text) in the main column and in this case leave A with the
digits and B with only the selected text.
Any further help please??
Dave

"David" wrote in message
...
I have a long column with numbers on the leftside of column then a space
then text. How do I remove the numbers to an adjacent column eg below

A1 B1
10 text xxxxx
111 textxxxx
112 textxxx etc
Would like:
A1 B1
10 textxxxxx
111 textxxxx
112 etc

regards Dave.

Try this sequence of action on a spare copy of your sheet

Put in C1 (Biff's formula) : =--LEFT(B1,FIND(" ",B1)-1)
Put in D1: =TRIM(MID(B1,FIND(" ",B1)+1,99))

Select C1:D1, fill down until the last row of data in col B

Col C gives you the numeric parts, col D returns the text parts

Now just select cols C and D,
and do a copy paste special as values to overwrite cols A and B
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"David" wrote in message
...
Biff, Thankyou for your formula, the only thing is that it does NOT remove
the digits from the main cell (B1 etc), only copies them. So if I use a
LEFT or MID etc, then manually wipe out the digits left in the B column
then
the results collapse in the A column. I want if possible to once and for
all
remove digits(or text) in the main column and in this case leave A with
the
digits and B with only the selected text.
Any further help please??
Dave

Now just select cols C and D,
and do a copy paste special as values to overwrite cols A and B

... and then delete cols C and D <g
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

try this
Sub splitemup()
For Each c in range("b2:b"&cells(rows.count,"b").end(xlup).row)' Selection
x = InStr(c, " ")
c.Offset(, 1) = Right(c, Len(c) - x)
c.Offset(, 0) = Left(c, x - 1)
Next
End Sub

--
Don Guillett
SalesAid Software

"David" wrote in message
...
I have a long column with numbers on the leftside of column then a space
then text. How do I remove the numbers to an adjacent column eg below

A1 B1
10 text xxxxx
111 textxxxx
112 textxxx etc
Would like:
A1 B1
10 textxxxxx
111 textxxxx
112 etc

regards Dave.

Hi David,
If you don't already have your answer, such as if the data
is not actually separated by a space, you might also take a
look at

Sorting, Some notes on sorting in Excel
http://www.mvps.org/dmcritchie/excel/sorting.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"David" wrote in message ...
I have a long column with numbers on the leftside of column then a space
then text. How do I remove the numbers to an adjacent column eg below

A1 B1
10 text xxxxx
111 textxxxx
112 textxxx etc
Would like:
A1 B1
10 textxxxxx
111 textxxxx
112 etc

regards Dave.