Remove Numbers from text

  • Thread starter Thread starter David
  • Start date Start date
D

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