Simplifies the delimiters

[kayza]

Hi, all

I try to simplify my data which has an uncertain number of delimiters. sample data as follows
A1 : one-two--three---four----five-----
the results I expect are like this
B1 : one-two-three-four-five

thanks in advance

 

[mikerickson]

try

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

 

[Peter_SSs]

1. Note Mike's suggestion has a typo. It should be
=SUBSTITUTE(TRIM(SUBSTITUTE(A1,"-"," "))," ","-")

2. If your data could actually contain spaces (eg "Green apple-red apple----ripe banana") then this extension of Mike's formula could be used
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1," ","#"),"-"," "))," ","-"),"#"," ")

 

[Rick Rothstein]

2. If your data could actually contain spaces (eg "Green apple-red apple----ripe banana") then this extension of Mike's formula could be used
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1," ","#"),"-"," "))," ","-"),"#"," ")

@kayza,

If your text could possibly have a # sign in it, then change the red highlighted # signs in Peter's formula to a different symbol that would never appear in your text. Alternately, you could use this variation to Peter's formula that should work with any text that you might normally be expected to have...

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