Hello All,



I am after a macro that will overwrite that data in selected cells.

I regularly copy a heading from a pdf and paste it into column G of an Excel register.
The length and content of the data being copied is never consistent.

I have found and modified some formulas that do what I am after, but this means I need a helper column and then copy the data back as values.

I would like to be able to select one or more cells and run the macro, so it edits the data in place.

The data is initially separated by en dashes then dashes, and finally an em dash. It is not unusual to find one or more spaces before or after the en dash or none at all.
The en dashes require a single space before and after each en dash and only the en dashes (not dashes or em dashes).

Initially I was only trying to add the spaces to the first three en dashes, hence my various formulas, then I realised the later parts of the headings only have dashes and an em dash, hence my other formulas.

I have also included a Function I have modified, but even though it works, I suspect I have not changed it correctly.

Any help or suggestion appreciated.

Regards, Dave T

Attached Files

Post - Add spaces before and after dashes.xlsm (21.9 KB, 3 views)

Code: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.

Hello p45cal,

Such a frustratingly simple solution that confirms I was overthinking the problem.
I also really appreciate your detailed explanation of what your macro is doing.

I had already worked out that LTrim and RTrim remove spaces from either end of the string, but not reducing any multiple mid spaces to a single space.
I had used SUBSTITUTE in a few of my earlier attempts and it worked, but each time I re-ran the macro on the same cells it kept adding extra spaces either side of the En Dash.
I then found APPLICATION.TRIM, but it seemed wrong to be adding multiple mid spaces in one part of the code and then removing them with another part of the code.

Once again… Thanks for your help and your explanation.
Regards, Dave T