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

=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

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

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.

"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

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

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.

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.