Add spaces before and after en dashes

p45cal · #2 04-24-2023, 04:54 PM

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Is there a way to use dashes in IF statement merges	shaggy30005	Mail Merge	1	07-14-2020 10:29 PM
Add dashes to each line of a list	Tomperm	Word	2	02-15-2017 05:48 PM
changing colons into dashes	retrofan	Word	2	07-27-2014 05:05 AM
letters are dashes	pmd	Word	5	03-05-2012 08:08 PM
Why word does not wrap on dashes?	lyrebird	Word	3	06-23-2010 01:32 AM