Code:
Code:
Sub blah()
Dim cll
For Each cll In Selection.Cells
cll.Value = Application.Trim(Replace(cll.Value, "–", " – "))
Next cll
End Sub
Select the cells you want processed (they don't have to be contiguous) then run the
blah macro.
Same as worksheet formula
=TRIM(SUBSTITUTE(A4,"–"," – "))
(Using SUBSTITUTE without a 3rd parameter substitutes all instances)
The APPLICATION.TRIM function used in vba is the same as the worksheet function; it not only trims spaces off the start and end of the string, it also reduces multiple spaces within the string to a single space.
The SUBSTITUTE (
Replace in vba) adds a space either side of each en dash, regardless of whether there were already spaces there. The TRIM later removes all but one space.
This means if there are multple spaces elsewhere in the source string, they too will be reduced to a single space.