Need help with find and replace code

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I have a spreadsheet where sheet 1 contains a few thousand rows of
text and sheet 2 contains a table where column A has the old text and
column B has the new text (there are approx 200 rows in this table).
I need to be able to do a systematic find and replace on sheet 1 with
all of the rows in the table on sheet 2. Can this be done? I have
look at previous posted messages and I cannot see any references to
findng and replacing where the find and replace values are spread over
many rows.

Any help would be appreciated.

Thanks
Scott
 
Hi Scott
a non VBA solution could be the following:
If sheet 1 contains your old information in column A try the following
- enter the following formula in B1:
=VLOOKUP(A1,'sheet2'!$A$1:$B$300,2,0)
- copy this formula down for all rows (or just simply double-click on
the lower right corner of the B1 cell selection)
- after this copy column B (e.g. with CTRL+C)
- goto 'Edit - Paste Special' and choose 'Values'
- after this delete the old column A
 
Thanks Frank. Unfortunately this won't work as the old text is
contained within a text string so I first need to assess the string to
see if it contains the old text and if so replace with the new text.
This needs to be done about 200 times for each string (about 2000
strings).

Scott
 
Back
Top