Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How-To Remove Extra Spaces
I having to clean up messy user entries and would need some vba help on
how-to approach the following: I am looping through a worksheet, row by row to get a column's value (this works). Now I would need to perform a cleanup on the cell's value. The user's have, in certain instances put in 2, 3 ... extra spaces in the value (name of people). and I need to remove the extra space so that I am left with firstname space lastname, instead of firstname space space lastname, etc... I might have 'John Smith' 'John Smith' When I need to clean it up so I have 'John Smith' This is my loop code, but I am lost on how to clean up the OpenedBy variable For i = 2 To lstRow OpenedBy = Range("J" & i).Text 'What would I need to do to remove extra spaces from the OpenedBy variable? Next i Thank you, QB |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How-To Remove Extra Spaces
Some more googling and I found a function that works great.
http://www.andreavb.com/tip000015.html QB "QB" wrote: I having to clean up messy user entries and would need some vba help on how-to approach the following: I am looping through a worksheet, row by row to get a column's value (this works). Now I would need to perform a cleanup on the cell's value. The user's have, in certain instances put in 2, 3 ... extra spaces in the value (name of people). and I need to remove the extra space so that I am left with firstname space lastname, instead of firstname space space lastname, etc... I might have 'John Smith' 'John Smith' When I need to clean it up so I have 'John Smith' This is my loop code, but I am lost on how to clean up the OpenedBy variable For i = 2 To lstRow OpenedBy = Range("J" & i).Text 'What would I need to do to remove extra spaces from the OpenedBy variable? Next i Thank you, QB |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How-To Remove Extra Spaces
WorksheetFunction.Trim will do that in a single statement (see my other
response in this thread). -- Rick (MVP - Excel) "QB" wrote in message ... Some more googling and I found a function that works great. http://www.andreavb.com/tip000015.html QB "QB" wrote: I having to clean up messy user entries and would need some vba help on how-to approach the following: I am looping through a worksheet, row by row to get a column's value (this works). Now I would need to perform a cleanup on the cell's value. The user's have, in certain instances put in 2, 3 ... extra spaces in the value (name of people). and I need to remove the extra space so that I am left with firstname space lastname, instead of firstname space space lastname, etc... I might have 'John Smith' 'John Smith' When I need to clean it up so I have 'John Smith' This is my loop code, but I am lost on how to clean up the OpenedBy variable For i = 2 To lstRow OpenedBy = Range("J" & i).Text 'What would I need to do to remove extra spaces from the OpenedBy variable? Next i Thank you, QB |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How-To Remove Extra Spaces
Select the range and run the below macro..which will remove all extra spaces..
For Each cell In Selection cell.Value = WorksheetFunction.Trim(cell.Text) Next If this post helps click Yes --------------- Jacob Skaria "QB" wrote: I having to clean up messy user entries and would need some vba help on how-to approach the following: I am looping through a worksheet, row by row to get a column's value (this works). Now I would need to perform a cleanup on the cell's value. The user's have, in certain instances put in 2, 3 ... extra spaces in the value (name of people). and I need to remove the extra space so that I am left with firstname space lastname, instead of firstname space space lastname, etc... I might have 'John Smith' 'John Smith' When I need to clean it up so I have 'John Smith' This is my loop code, but I am lost on how to clean up the OpenedBy variable For i = 2 To lstRow OpenedBy = Range("J" & i).Text 'What would I need to do to remove extra spaces from the OpenedBy variable? Next i Thank you, QB |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How-To Remove Extra Spaces
Use the Trim worksheet function, like this...
OpenedBy = WorksheetFunction.Trim(Range("J" & i).Text) -- Rick (MVP - Excel) "QB" wrote in message ... I having to clean up messy user entries and would need some vba help on how-to approach the following: I am looping through a worksheet, row by row to get a column's value (this works). Now I would need to perform a cleanup on the cell's value. The user's have, in certain instances put in 2, 3 ... extra spaces in the value (name of people). and I need to remove the extra space so that I am left with firstname space lastname, instead of firstname space space lastname, etc... I might have 'John Smith' 'John Smith' When I need to clean it up so I have 'John Smith' This is my loop code, but I am lost on how to clean up the OpenedBy variable For i = 2 To lstRow OpenedBy = Range("J" & i).Text 'What would I need to do to remove extra spaces from the OpenedBy variable? Next i Thank you, QB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing extra spaces in a string | Excel Programming | |||
removing extra spaces | Excel Discussion (Misc queries) | |||
removing extra spaces in excel | Excel Programming | |||
how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
deleting extra spaces in a string | Excel Programming |