How to Remove the 2nd Comma within a cell?

[carolcake]

Good Evening everyone,

I'm trying to remove the 2nd comma in a CSZ (City, State, Zip Code) cell of information.

Below is an example of the data.

TEMECULA, CA, 92592

Lake Elsinore, CA, 92532

LAKESIDE, CA, 92040

LAKESIDE, CA, 92040

Blythe, CA, 92225

Vista, CA, 92081

Twentynine Palms, CA, 92277

PAUMA VALLEY, CA, 92061

Chula Vista, CA, 91911 --The Goal would be to remove the commas between CA and the zipcode. This should let me import the file into Convey. Any help from you guys would be much appreciated. Thank you

<tbody>
</tbody>

 

[prince141987]

Just paste your string into A1 and wirte the formula in B1 and Enjoy.

=SUBSTITUTE(A1,",","",2)

Regards
prince

 

[Misca]

Welcome to the message board!

Try using the SUBSTITUTE-function:

=SUBSTITUTE(Text, Old text, New text, Instance number)
=SUBSTITUTE(A1,",","",2)

Another alternative would be using the text to columns command with comma as the delimiter.