How to Remove the 2nd Comma within a cell?

carolcake

New Member
Joined
Apr 19, 2013
Messages
1
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>
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top