remove multiple set of numbers from column

Wt2010 · January 8th, 2010, 04:35 PM

I have a column with data like this and I need to remove all instances of these numbers and the space.

I have this:
222 Burgundy, 429 Light Gray, 266 Purple, 186 Red, 316 Dark Teal, 021 Orange, 155 Ivory, Process Blue, 478 Brown, White, 348 Green, Reflex Blue, Black, 282 Dark Blue, 872 Metallic Gold

I need this:
Burgundy, Light Gray, Purple, Red, Dark Teal, Orange, Ivory, Process Blue, Brown, White, Green, Reflex Blue, Black, Dark Blue, Metallic Gold

I really appreciate someone can help me on this.

Thanks

Shasur · January 9th, 2010, 10:10 PM

Here is one way of doing it:

Code:

Sub Clean_Numbers()
Dim sNumber
For i = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
    sNumber = Trim(Left(Cells(i, 1).Value, InStr(1, Cells(i, 1).Value, " ")))
    If IsNumeric(sNumber) = True Then
        Cells(i, 2).Value = Mid(Cells(i, 1).Value, InStr(1, Cells(i, 1).Value, " ") + 1, Len(Cells(i, 1).Value))
    End If
    
Next i
End Sub

Cheers
Shasur

stanleydgromjr · January 9th, 2010, 11:10 PM

Wt2010,

If your string data is in column A, beginning in row 1, then the resulting string after removing leading numbers will be in column B.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).
Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

 
Option Explicit
Sub RemoveNbrs()
' stanleydgromjr, 20100109
Dim c As Range, MyS As Long
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  MyS = 0
  On Error Resume Next
  MyS = WorksheetFunction.Find(" ", c, 1)
  On Error GoTo 0
  If MyS = 0 Then
    c.Offset(, 1) = c
  ElseIf Not IsNumeric(Left(c, MyS - 1)) Then
    c.Offset(, 1) = c
  Else
    c.Offset(, 1) = Right(c, Len(c.Value) - WorksheetFunction.Find(" ", c, 1))
  End If
Next c
Application.ScreenUpdating = True
End Sub

Then run the "RemoveNbrs" macro.

gbianchi · January 11th, 2010, 09:22 AM

MMM.. VBA uses vb5 or vb6?? because there is a replace function that could do this more simple.... because neither of you is removing spaces ;)

Shasur · January 12th, 2010, 08:29 AM

Hi

VBA has the replace function too

Cheers
Shasur

Wt2010 · January 12th, 2010, 08:18 PM

Hello Guys,

Thank you for these recommendations. I have tested both codes and they work... partially. Both removes only the first set of numbers before the first comma, after the first comma, everything goes the same.

This:
222 Burgundy, 429 Light Gray, 266 Purple, 186 Red, 316 Dark Teal, 021 Orange, 155 Ivory, Process Blue, 478 Brown, White, 348 Green, Reflex Blue, Black, 282 Dark Blue, 872 Metallic Gold

Change to this:
Burgundy, 429 Light Gray, 266 Purple, 186 Red, 316 Dark Teal, 021 Orange, 155 Ivory, Process Blue, 478 Brown, White, 348 Green, Reflex Blue, Black, 282 Dark Blue, 872 Metallic Gold

Thanks.

Shasur · January 13th, 2010, 06:01 AM

Hi

Never thought all the text in one cell:

Can you check if the following works for you

Code:

Sub Clean_Numbers_2()
Dim sText
Dim i, j
For i = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
    sText = Trim(Cells(i, 1).Value)
    For j = 0 To 9
        sText = Replace(sText, j, "")
    Next j
    sText = Replace(sText, "  ", " ")
    Cells(i, 2).Value = Trim(sText)
Next i
End Sub

Cheers
Shasur

stanleydgromjr · January 15th, 2010, 12:04 AM

Wt2010,

If this is in cell A1:
222 Burgundy, 429 Light Gray, 266 Purple, 186 Red, 316 Dark Teal, 021 Orange, 155 Ivory, Process Blue, 478 Brown, White, 348 Green, Reflex Blue, Black, 282 Dark Blue, 872 Metallic Gold

In cell B1 enter this formula:
=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

To give you in cell B1:
Burgundy, 429 Light Gray, 266 Purple, 186 Red, 316 Dark Teal, 021 Orange, 155 Ivory, Process Blue, 478 Brown, White, 348 Green, Reflex Blue, Black, 282 Dark Blue, 872 Metallic Gold

If the above is still not correct, then can we have a sample workbook. You can upload it to www.box.net and provide us with a link to your workbook.

Have a great day,
Stan

Wt2010 · January 22nd, 2010, 09:17 PM

It worked! - Thanks