Need Help Adding Spaces

[dwgnome]

Need assistance in adding a space after the first word and before the dash and one space after the dash. All strings are in one cell. For example Paint-Primer GX becomes Paint - Primer GX by adding a space on either side of the dash. Sometimes a space will already be on one side of the dash but not the other. So here are the possible scenarios:

Paint -Topcoat 5600 (need just one space after the dash)
Chemical- Bonderite Ready Set (need a space before the dash)
Powder - Metco 100 (no adjustment necessary)
Paint-Polyurethane (need space on both sides of the dash)

Also, there may be other dashes later in the string, so just need the first occurrence to be adjusted.

Excel 2003.

 

[dwgnome]

Thank you all for replying. You saved me a lot of time not having to manually adjust the spacing. I ended up using gaz_chops version as it is concise and does the job perfectly. In fact I forgot to mention that if there is more than one space before or after, that those should be removed. This formula does that while AlanY works for most cases, however it does not remove the extra spaces if any occur.

Azumi, not sure what your first formula is doing, but it seems to chop off rest of string on some of them as I copy down. My data actually starts at D20 so not sure how to deal with COLUMN range or why I would need that since I am only interrogating the cell to the left. Of course your second version is similar to and works like gaz_chops' version.

oldbrewer, thanks again for chiming in. Very interesting approach, but not wanting to actually result in a text statement, just make the change itself.

 

[gaz_chops]

You're welcome, thanks for the feedback.