Re: 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(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(...,",,",",")
>
>
> In article <npng81tcncp6hhn35qk8533uttprqo7vf6@xxxxxxx>,
> David Godinger <daveg7777777@xxxxxxxxxxxxxxxxxx> 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.


.

Relevant Pages
Quantcast