Macro to clean up data in column B

[tonywatsonhelp]

Hi Everyone,

Hope someone can help,

What i need is a macro that when run does the following,

Cleans up the data in Sheet "DATA" column B buy doing these things

first, remove all trailing zeros (i.e. trim the cells)
then make all the data "proper" so "MIKE smith" becomes "Mike Smith" etc.

then and this is the hardest part,
in sheets Control Column C (Excluding row1 as its the header) I have a list of words, In D i have a list of replacement words or it might be blank,
I want the macro to go down the list in Control Column C, one at a time and find the word in Sheet "DATA" column B and replace with what's in column D if its possible it would be perfect if in column E i could put "Whole" for the find and replace to just replace if it the whole cell and blank does any.

I hope that makes sense and someone can help me

Thanks

Tony

 

[DanteAmor]

first, remove all trailing zeros (i.e. trim the cells)

For that part (remove all trailing zeros), you can put a couple of examples of the content of the cells and what you want in the result.

Try this:

Sub clean_up_data_column_b()
  Dim b As Range, c As Range
  Dim xLookAt
  
  Application.ScreenUpdating = False
  With Sheets("Data")
    For Each b In .Range("B1", .Range("B" & Rows.Count).End(3))
      b.Value = WorksheetFunction.Proper(b.Value)
    Next
    For Each c In Sheets("Control").Range("C2", Sheets("Control").Range("C" & Rows.Count).End(3))
      .Range("B:B").Replace c, c.Offset(, 1), IIf(c.Offset(, 2) = "Whole", xlWhole, xlPart), , False
    Next
  End With
End Sub

 

[tonywatsonhelp]

Hi Dante,
This is great, thank you very much for your help.

All I want to do is run the TRIM Command on every cell before doing anything else.
I think I might be able to adapt the first part of your code to do that, but if you or anyone has a quick macro to run trim on a column that would be a great help.

Thanks

Tony

 

[DanteAmor]

All I want to do is run the TRIM Command on every cell

Try this:

Sub clean_up_data_column_b()
  Dim b As Range, c As Range
  Dim xLookAt
  
  Application.ScreenUpdating = False
  With Sheets("Data")
    For Each b In .Range("B1", .Range("B" & Rows.Count).End(3))
      b.Value = WorksheetFunction.Proper(b.Value)
      b.Value = WorksheetFunction.Trim(b.Value)
    Next
    For Each c In Sheets("Control").Range("C2", Sheets("Control").Range("C" & Rows.Count).End(3))
      .Range("B:B").Replace c, c.Offset(, 1), IIf(c.Offset(, 2) = "Whole", xlWhole, xlPart), , False
    Next
  End With
End Sub

 

[tonywatsonhelp]

Brilliant, thank you

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.