Addresses & Duplicates

[clappouk]

Hey all,

Hoping you can help with the following problem. I have inherited a spreadsheet where colleagues have been inputting addresses into 1 cell but they have not been using different format.

For Example

11 Smith Street London L1 1TT
11Smith Street Tottenham London L1
Flat 2 11 Smith Street London L1 1TT

Hope you get the idea, lots of variations of the same or similar addresses I think its upto 2400 rows of addresses. I have tried using Trim to remove all the spaces and then I thought of inserting a column at the end, then using the Search Function to look for chars 5-10 in each cell and place them in the column. Then I could filter on these to remove the duplicates. I am thinking this may not solve the whole problem but will help a great deal. Any Ideas as i dont know how to use the search function.

Thank you

Ray
Excel 2003

 

[Robert Mika]

Trim does not solve your problem as will only cut the first and last space.
Use =SUBSTITUTE(A1," ","")
to cut all spaces.
Then you can try in C1 use =SEARCH("11Smith",B2) to find addresses with "11Smith".