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, 5 views)