How to remove trailing & leading spaces in a cell?

p45cal · #6 02-04-2018, 12:20 PM

…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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Can I remove spaces between only Chinese characters?	sivartnosredna7	Word	2	01-09-2017 05:33 AM
How to remove spaces from Cells which are at the end of value?	LearnerExcel	Excel	2	12-19-2016 01:10 PM
Mail Merge - Trailing Spaces	osucjb	Mail Merge	5	10-28-2016 12:03 AM
Get rid of leading spaces before paragraphs	fig000	Word	11	03-14-2016 01:59 PM
editing text and remove spaces	romanticbiro	Word VBA	5	07-04-2014 07:42 PM