Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

excel

ResolvedVersion Standard

Rebecca has attended:
Access Intermediate course

Excel

Is there a simple way to clean up a column full of different addresses so that you only have the street name. I've tried text to columns, find and replace and copy/paste.
i.e
1 smith street london w1
flat 45 george street london w122lm
159 hermit road london w5

TO

smith street
george street
hermit road

thanks

RE: Excel

Hello Rebecca,
I hope you enjoyed your Access training session with us.

This is a very good question, and one that has me pondering what to do. You seem to have tried all the things I would have done as well. The only thing I can think of would be to do a find/replace on

<space>London*

(assuming the addresses are all in London - otherwise, you may need to run this multiple times). This will give you only the street address. Perhaps now the text to columns will be easier to cope with. Once you have all the streets in one column you can concatenate just the street name with "street/road/etc".

I hope this resolves your question. If it has, please mark this question as resolved. If you require further assistance, please reply to this post. And please feel free to ask any other questions you may have.

Have a great day.

Regards,
Mara
Microsoft Office Specialist Master Trainer

 

Training courses

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Missing Field handle

If your field handle goes missing all you need to do is go to tools > options > edit tab and then make sure that the check boxes for paste and insert buttons are checked.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.