Formula for Replacing Text in a String?

  • Thread starter Thread starter David Godinger
  • Start date Start date
D

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
 
=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
 
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
 
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)=",")),", ,","")
 
Peter T said:
=LEFT(SUBSTITUTE(A1,", , ",""),IF(RIGHT(SUBSTITUTE(A1,", ,
",""))=",",LEN(SUBSTITUTE(A1,", , ",""))-1,LEN(SUBSTITUTE(A1,", , ",""))))
Click to expand...

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.
 
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

David Godinger said:
",""))))

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.
Click to expand...
Martin King
 
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(...,",,",",")
 
That's excellent!

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

Regards,
Peter T

in message
 
Back
Top