…and it looks like you need to alter spaces around the
* character too.
The attached has a user-defined function
Spaces, whose code is:
Code:
Function Spaces(strText As String) As String
Spaces = Application.Trim(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Application.Trim(strText), " -", "-"), "- ", "-"), " *", "*"), "* ", "*"), "(", " ("), ")", ") "), "( ", "("), " )", ")")) 'needed?
End Function
This function can be used on the sheet directly (see sheet
Remove Spaces), or from a macro (see sheet
Remove Spaces (2))
Using it on a sheet is straightforward.
To use it from a macro, you first select the cells you want to process, then run the macro
blah. This macro is called for you when you click the button on sheet
Remove Spaces (2), but remember to select the cells you want processing before you click the button. It permanently replaces the contents of those cells.
On both sheets, there's a checking formula to demonstrate that the resulting values are exactly the same as desired.