Advanced Find and Replace

[lucydear]

Hi,

I'm only a basic Excel user so have no idea if what I want to do is possible or not, so apologies if this is totally incoherent.

I'm cleaning up data in Excel, and I'm working on >2000 rows so editing manually is really time consuming. I basically need the content of Column E (text) in my spreadsheet to match the content of Column A. The content of E does usually contain the content of A, but with extra data that I need to get rid of.

eg, Column A will contain 'Three Steps To The Ocean' and Column E will contain 'Three Steps to The Ocean - EU.TOUR Feb 25 - Mar 06'. I need to get rid of anything that isn't 'Three Steps To The Ocean.'

Is there a way of using Find and Replace to search each row in Column E, and if the content of the same row in Column A is found, to replace it with ONLY the content of Column A, so the extra data is removed?

Please ask me to clarify anything I haven't been clear on! Any help at all would be immensely appreciated.

[dominicb]

Good morning lucydear

...and welcome to the forum!!

The Find and Replace function won't work as you require. That's the top and bottom of it.

However, there may well be a solution that doens't involve find and replace. Why can't you just use in cell E1 the formula :
=A1
and copy it down? That would replicate the cntents of A1, which you've stated are usually the same, but without the extra info you want to get rid of. If this isn't appropriate post back stating why and someone we might be able to sort something out.

HTH

DominicB

[lucydear]

Hi DominicB, thanks for your reply. Wow, so simple! Can't believe I didn't think of that. I'm trying it out right now and it looks promising but will be in touch again if it doesn't work. Thanks very much.