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.

 

[gaz_chops]

Try

=TRIM(SUBSTITUTE(A1,"-"," - ",1))

 

[AlanY]

try

=SUBSTITUTE(SUBSTITUTE(A1,"-"," - ",1)," "," ")

 

[gaz_chops]

try

=SUBSTITUTE(SUBSTITUTE(A1,"-"," - ",1)," "," ")

Think that will leave extra spaces.

 

[azumi]

Just for your examples, and Im assumed your text start on A2 :

=TRIM(SUBSTITUTE(LEFT(A2,MIN(FIND(CHAR(64+COLUMN($A$1:$W$1)),MID(A2,2,255)&(CHAR(64+COLUMN($A$1:$W$1)))))),"-"," - "))

 

[AlanY]

Think that will leave extra spaces.

should be ok, the 2nd sub replaces the double spaces with a single

just realised that it didn't shown correctly

=SUBSTITUTE(SUBSTITUTE(A1,"-"," - ",1),"  "," ")

 

[azumi]

or simple

=TRIM(SUBSTITUTE(A2,"-"," - "))

 

[gaz_chops]

should be ok, the 2nd sub replaces the double spaces with a single

Except it doesn't have a dble space,

 

[AlanY]

Except it doesn't have a dble space,

interesting.

it's not showing on post #3, but if you do a reply with quote then it does!

 

[oldbrewer]

		Paint -Topcoat 5600	needtrailing	####
		Chemical- Bonderite Ready Set	needleading
		Powder - Metco 100	leave
		Paint-Polyurethane	needboth
		it’s a start….
		####
=IF(MID(C9,SEARCH("-",C9)-1,3)=" - ","leave",IF(MID(C9,SEARCH("-",C9),2)="- ","needleading",IF(MID(C9,SEARCH("-",C9)-1,2)=" -","needtrailing","needboth")))

<colgroup><col><col><col><col><col span="7"></colgroup><tbody>
</tbody>