Formula for Replacing Text in a String?

[David Godinger]

I'd like to make a formula (NOT USING VBA) that would replace, in a list of
characters, all instances of the following with nothing:

, ,


(That's a comma followed by a space, followed by a comma, followed by a
space.)

If possible, the formula would also eliminate the last character in the
string if it's a comma.

Thanks,

Dave

 

[Peter T]

=IF(LEFT(A1,4)=", , ",MID(A1,5,LEN(A1)-5),A1)

You did say remove the last character?
Above errors if there is not at least 1 character following your string.
Fixable if needs but a longer formula.

Regards,
Peter T

 

[Peter T]

I misread the question! try -

=LEFT(SUBSTITUTE(A1,", , ",""),IF(RIGHT(SUBSTITUTE(A1,", ,
",""))=",",LEN(SUBSTITUTE(A1,", , ",""))-1,LEN(SUBSTITUTE(A1,", , ",""))))

on one line

Peter T

Peter T wrote in message

 

[JE McGimpsey]

First, if you don't want VBA, the .programming group is not the
appropriate newsgroup to post in. Try .worksheet.functions next time.

One way:

=SUBSTITUTE(LEFT(A1,LEN(A1)-(RIGHT(A1,1)=",")),", ,","")

 

[David Godinger]

=LEFT(SUBSTITUTE(A1,", , ",""),IF(RIGHT(SUBSTITUTE(A1,", ,
",""))=",",LEN(SUBSTITUTE(A1,", , ",""))-1,LEN(SUBSTITUTE(A1,", , ",""))))

This does almost everything I want, but I made a mistake in expressing
myself. I don't want to be left with nothing. I always want to leave one
instance of ", "

Whether I'm substituting ", , , , , " or ", , " I always want to be left
with ", "

Thanks again if you have the time.

 

[Peter T]

Could you give examples of what you start with and what you want to end up
with. Show potential locations of your string's ", , "
Still need to loose any trailing comma, and possible trailing space?

JE's & my formulas appear to do same, as his is far more elegant probably
best to try and develop his. However I suspect a solution will be much
easier with a macro, assuming it's even possible with a worksheet formula.

Regards,
Peter T

",""))))

This does almost everything I want, but I made a mistake in expressing
myself. I don't want to be left with nothing. I always want to leave one
instance of ", "

Whether I'm substituting ", , , , , " or ", , " I always want to be left
with ", "

Thanks again if you have the time.

Martin King

 

[JE McGimpsey]

One way:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,LEN(A1)-(
RIGHT(A1,1)=","))," ,",","),",,",","),",,",","),",,",","),",,",",")

If this isn't enough to get rid of all the extra commas, just wrap it in
another

SUBSTITUTE(...,",,",",")

 

[Peter T]

That's excellent!

I was right about it being best to develop your formula!

Regards,
Peter T

in message