Ask Your Question

advanced find and replace (150 different F&R values)

asked 2015-07-10 21:10:26 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

say i got 32000 cells, 1 column. I want to be able to find and replace value 1 with x value 2 with y value 3 with z

i have 150 different values. is there a way to automate this? or will i have to run ctrl h 150 times manually?

edit retag flag offensive close merge delete


Please explain a bit maybe regular expression could help. Several values to replace with the same or one by one, or ..?

m.a.riosv gravatar imagem.a.riosv ( 2015-07-10 23:18:18 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2015-07-12 07:32:34 +0200

pierre-yves samyn gravatar image


If I understand the question you can get the result in B with :

  • A: Initial data
  • D: Data to search
  • E: Replacement data
  • B, Formula: =IF(SUMPRODUCT(A2=$D$2:$D$4)=0;A2;INDEX($E$2:$E$4;MATCH(A2;$D$2:$D$4;0)))




If this answers the question, thank you to click on the ✔ to mark it ANSWERED

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-07-10 21:10:26 +0200

Seen: 685 times

Last updated: Aug 18 '20