Roderick_E
Well-known Member
- Joined
- Oct 13, 2007
- Messages
- 2,051
I work with lots of data that must be matched against other lists of data so I need to make sure the data is clean (no leading/trailing spaces, no commas/periods)
I worked on a macro that allows me clean up the leading/trailing spaces of a selected column:
A couple of problems with my code.
#1 If a person selects an entire column, the code will run all the way through the 65thousand or so rows (too much, it should determine the last populated row & only run as far as that)
#2 I'd like it to also replace commas & periods with spaces & then delete out the extra spaces. I can write a formula that does such a thing but I don't know how the macro syntax works -- see formulas below:
Replace commas -- find position of comma & replace with space, trim space
Replace period -- find position of period & replace with space, trim space
Thanks for the help
I worked on a macro that allows me clean up the leading/trailing spaces of a selected column:
Code:
Sub trimall()
'select ONLY range to trim & run this macro. Will remove all leading & trailing spaces
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
For Each cel In Selection
cel.Value = Application.WorksheetFunction.Trim(cel.Value)
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
MsgBox ("Trim Complete")
End Sub
A couple of problems with my code.
#1 If a person selects an entire column, the code will run all the way through the 65thousand or so rows (too much, it should determine the last populated row & only run as far as that)
#2 I'd like it to also replace commas & periods with spaces & then delete out the extra spaces. I can write a formula that does such a thing but I don't know how the macro syntax works -- see formulas below:
Replace commas -- find position of comma & replace with space, trim space
Code:
=IF(ISERROR(TRIM(REPLACE(A1,SEARCH(",",A1),1," "))),A1,TRIM(REPLACE(A1,SEARCH(",",A1),1," ")))
Replace period -- find position of period & replace with space, trim space
Code:
=IF(ISERROR(TRIM(REPLACE(A1,SEARCH(".",A1),1," "))),A1,TRIM(REPLACE(A1,SEARCH(".",A1),1," ")))
Thanks for the help