faster way to clean up a database?

[samanthamcleish]

I am trying to clean up a data base and was wondering if you knew a faster way to do it. The addresses have extra commas which doesnt allow the address to show in the next column properly until the comma is removed.

 

[kpark91]

Select the column and press Ctrl + H
then replace "," with "" then replace all (without the quotatiosn)

Test it on the copy of your workbook.

 

[samanthamcleish]

I dont want to remove all of the commas though

 

[kpark91]

Then how about integrating

SUBSTITUTE(yourcell, ",", "") into your next column formula?

If you need help integrating it, post your formula here.

 

[samanthamcleish]

I have the columns set up.

for example:
1 McCallum Drive Richmond Hill,, ON L4C 7T3

there is an extra comnma in this one along with many others and I need a way to be able to remove them without doing it mannually.

Also this example
7, Rue Longtin, #6 Saint-Constant, Que. J5A 1C4

it has 3 extra commas.

 

[kpark91]

Yes... =SUBSTITUTE (yourcell, ",", "") replaces all the occurrences of "," with "" in yourcell. However, the values of the inputs will not change meaning you will not permanently destroy the data and It is not doing it manually.

Try creating a helper column and type
=SUBSTITUTE(yourcell, ",", "") and drag down.
then see for yourself.

 

[samanthamcleish]

the problem with that is it removes all of the commas i dont want to remove the commas before the province abbrevation

 

[kpark91]

I'm afraid I don't know then..

 

[ChrisOswald]

Let me see if I'm reading you question correctly: In your addressing information do you want ONLY the commas that precede the Province abbreviation?

 

[shg]

How about =SUBSTITUTE(A1, ",,", ",")