Remove Numbers from text

[David]

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]

Hi!

Try this in A1 and copy down as needed:

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

Biff

 

[David]

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

 

[Max]

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 C11, 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

 

[Max]

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>

 

[Don Guillett]

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

 

[David McRitchie]

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