Not an Expert and need an Automated find & search solution!

[HottubMick]

Hi Guys,

i am really hoping someone can help me find a solution and save hours of manual finding and searching from 2300 rows.

Column A shows a list of place names and the rest of the columns D, G, H, I and K have content within them that has another place name that needs to be changed to the same as column A. I know i can manually select a row, find and search, cut and paste the correct name from column A into it and replace all. The issue is i have 2300 rows and each place name down the list in column A is different so i would have to manually click on each row/find and search 2300 times!

I am hoping someone here in the know can help me to automate the find and search of each row and use the place name from Column A to replace all others in the same row from other columns.

I have attached a portion of the spreadsheet, the first couple of rows i have done manually to show what they should be like.

Please help!!

[HansDouwe]

Please try in D2 and copy down:

Please Login or Register  to view this content.

Similar solutions apply to the other columns

[HottubMick]

Thank you so much for this!

I am not sure the description column will be so easy as there are 5 of the wrong place name within the text in different locations. I am grateful for your help so far!

Mick

[HansDouwe]

Thanks for the feedback. Glad to help. If there any questions left, please ask them here.

[HottubMick]

Thank you, i can't get this to work on the other columns, description column G has 5x the names within a load of text and i dont know how i would replace all 5 with this method. I have also tried column I and as it has | Duchy Detectors UK after the place name from Column A i can't get it to work. Please could you show me a solution to the other columns?

[HansDouwe]

Solution column I Try in I2 and copy down

Please Login or Register  to view this content.

Solution column G is a little bit more work, because you have to split up the text in small portions of max 256 characters and concatenate these with =CONCAT. Also you should replace all " by "", otherwhise Excel does not understand you need a " in the text.

[HottubMick]

many thanks, Column I all done!

Sorry to be a further pain, please could you give me an example of what you mean for column G.

[HottubMick]

I have now broken up column G into several columns, my first column is

<h2 style="text-align: center;"><span style="color:#980000;"><strong>Metal Detectors in Abbess Roding</strong></span></h2><p style="text-align: center;"><span style="font-size:14px;">Welcome to Duchy Metal Detectors and the world of UK metal detecting.

But i cant for the life of me get =" and "&A2&" and " to work on the above.

[HansDouwe]

Please try

Please Login or Register  to view this content.

If you put quotation marks around a text and the text itself also contains quotation marks, you must first change those quotation marks to double quotation marks. Otherwise, Excel won't know that you need a quotation mark in your text.

[HottubMick]

Thank you so much, i managed to do it with your advice and as you said it was a little tricky but all done.

You saved me hours of work and i can't thank you enough, you have proven to me that kind people still exist out there.

Have a great day!

Mick.

[HansDouwe]

You are welcome. Glad to have helped and thanks for your feedback.