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

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