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

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

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

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

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