Removing Excess Spaces in a String

[madhuchelliah]

Hello all, i want to remove more than one space between the contents in the string. The string is always in the last used cell of H column. The string contains both single and double spaces. The macro should delete the double space alone and leave the single space as it is. Please heads up. Thank you.

 

[Fluff]

Try

Sub RemBlank()
With Range("H" & Rows.count).End(xlUp)
   .Value = WorksheetFunction.trim(Range("H" & Rows.count).End(xlUp).Value)
End With
End Sub

 

[madhuchelliah]

Hello Fluff, its working great. Could you please look into the similar one. In case if there is now spaces between characters how to add spaces between the characters in the string. The macro should add space between each character.If already space is exist it should ignore and add only where space is required. The string is in last used row of H column. Thank you.

 

[Fluff]

As this is a completely different question, can you please start a new thread?

 

[madhuchelliah]

Sure Fluff. Thank you.