Clean up data in Excel
I have an Excel file I need to clean up some data
This is what I have and need to know how to remove unnecessary black spaces.
Suite 22 - 222 Anywhere Street
I don not want to remove the space in Suite 22 and so on. The field size are all different and spaces. I have attached an excel sample.
Expert-Exchange-Sample.xlsx
Can someone help me with this?
This is what I have and need to know how to remove unnecessary black spaces.
Suite 22 - 222 Anywhere Street
I don not want to remove the space in Suite 22 and so on. The field size are all different and spaces. I have attached an excel sample.
Expert-Exchange-Sample.xlsx
Can someone help me with this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Forgot something:
You have to make a macro first that is called NoSpaces and paste the code in I provide above.
Do you know C/C++?
You can save the file as a comma-delimited csv file and write some code that would clean up the spaces and save it back as a txt file that can be re-opened as a csv.
But that may be a bit longer than what you're looking to do.
You can save the file as a comma-delimited csv file and write some code that would clean up the spaces and save it back as a txt file that can be re-opened as a csv.
But that may be a bit longer than what you're looking to do.
ASKER
Thank you rorya! That was very simple indeed...
Sub NoSpaces()
Dim c As Range
For Each c In Selection.Cells
Do
c = Replace(c, " ", " ")
Loop While InStr (1,c.Text," ")
Next
End Sub
Select the cells you want to modify, and then run the macro. It examines each cell in the selected range, removing any spaces in that range. The result is then placed back in the same cell.