View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.newusers
alistairp alistairp is offline
external usenet poster
 
Posts: 4
Default Find and replace

vlookup does the trick. Many thanks for your help.

Alistair

"Max" wrote:

Perhaps a simpler process using vlookup would suffice?

Reference list is assumed in sheet: xlator, cols A and B
where col A = items to find, col B = what to replace it with

With source data assumed in A2 down in the other sheet
Put in B2:
=IF(A2="","",IF(ISNA(VLOOKUP(A2,xlator!A:B,2,0)),A 2,VLOOKUP(A2,xlator!A:B,2,0)))
Copy B2 down to the last row of data in col A. Then copy col B, overwrite
col A with a paste special as values. Delete col B.


If you want to pursue the sub approach, try a post in .programming. Or hang
around here awhile, maybe other responders versed in vba might chime in.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"alistairp" wrote:
Thanks Max

This works until the final step when all data within the worksheet is being
amended.

I need to Macro to say something similar to the tick box in Find & Replace
that says 'Match entire cell contents' only.

For example I am amending OS to read OXFORD STREET. OS is in Column A and
despite highlighting this column when I click Run all text in the spreadsheet
is altered. The word MOSAIC is being amended to MOXFORDSTREETAIC and then it
is adding READING in place of RD and giving me MOXFOREADINGSTREETAIC.

Presumably I have missed something.

Any further assistance would be appreciated.

Alistair