Formula for Replacing Text in a String?
 

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

--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

Formula for Replacing Text in a String?
 

=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'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

Formula for Replacing Text in a String?
 

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
=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'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

Formula for Replacing Text in a String?
 

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)=",")),", ,","")




In article ,
David Godinger wrote:

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.

Formula for Replacing Text in a String?
 

"Peter T" <peter_t@discussions wrote:

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


on one line

Peter T

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





--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

Formula for Replacing Text in a String?
 

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" wrote in message
...
"Peter T" <peter_t@discussions wrote:

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


on one line

Peter T

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





--
Please delete "ANTI-SPAM" from email address

David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr.
Martin King

Formula for Replacing Text in a String?
 

One way:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(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(...,",,",",")


In article ,
David Godinger wrote:

"Peter T" <peter_t@discussions wrote:

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

Formula for Replacing Text in a String?
 

That's excellent!

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

Regards,
Peter T

"JE McGimpsey" wrote in message

One way:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(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(...,",,",",")


In article ,
David Godinger wrote:

"Peter T" <peter_t@discussions wrote:

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