Separating characters with a hyphen

[colarguns]

Hi,

I have a couple of examples, that need help on separation.

In Column A, I have: Mystique - 2'6" x 8' Runner

In Column B, I am needing the desired result of: Mystique-2'6"-x-8'-Runner


There are some also, that contain a different variation, such as:

In Column A, I have: Mystique - 18" Sample Area Rug

In Column B, I am needing the desired result of: Mystique-18"-Sample-Rug


Pretty much, all text or numbers should have a dash separating each, with no spaces. I have tried a few formulas, with no success, plus I ended up going the text to column route, and then trying to reassemble, with no success. Any help would be appreciated.

 

[Eric W]

Try:

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

 

[Rick Rothstein]

There are some also, that contain a different variation, such as:

In Column A, I have: Mystique - 18" Sample Area Rug

In Column B, I am needing the desired result of: Mystique-18"-Sample-Rug

What happened to the word "Area"... did you omit it on purpose or by accident? If on purpose, what it the rule for omitting text?

 

[colarguns]

Try:

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




That fixed it up nicely! Thank you very much!

 

[colarguns]

That was by accident sir, thanks for pointing that out. Enjoy your week sir