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.

 

[tushiroda]

note that the 15 in the 2nd formula assumes that the state/zipcode (or whatever you call it in canada) are roughly fixed in length. you should change that number to what makes sense. hope it helps some.

Excel Workbook
	B	C
3	1 McCallum Drive Richmond Hill,, ON L4C 7T3	1 McCallum Drive Richmond Hill, ON L4C 7T3
4	7, Rue Longtin, #6 Saint-Constant, Que. J5A 1C4	7 Rue Longtin #6 Saint-Constant, Que. J5A 1C4
Sheet2

Excel 2007

Cell Formulas
Range	Formula
C3	=SUBSTITUTE(B3,",,",",")
C4	=SUBSTITUTE(LEFT(B4,FIND(",",B4,LEN(B4)-15)-1),",","") &","& RIGHT(B4,LEN(B4)-FIND(",",B4,LEN(B4)-15))