Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 ---- |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i remove hyphens from between numbers | Excel Worksheet Functions | |||
How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) | |||
Remove leading Numbers from text | Excel Worksheet Functions | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) |