Macro to clean up data in column B

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,037
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,823
Office Version
  1. 2010
Platform
  1. Windows
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:

VBA Code:
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

Well-known Member
Joined
Feb 24, 2014
Messages
3,037
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
14,823
Office Version
  1. 2010
Platform
  1. Windows
All I want to do is run the TRIM Command on every cell
Try this:

VBA Code:
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
 
Solution

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,823
Office Version
  1. 2010
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,579
Messages
5,838,201
Members
430,534
Latest member
chacc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top