• Skip to primary navigation
  • Skip to main content

iSensei.tv

High Quality Apple and Tech Training

  • Productivity & Apple Tips
  • Apple in Education
  • Join 8700+ Online Students at Udemy
    • Effortlessly Switch from PC to Mac
    • Master the Basics of Using a Mac
    • Leverage Dropbox Cloud Storage
    • iPhone 101 (iOS 7 Version)
    • Manage WordPress from an iPad or Mac
  • About
    • Why iSensei.TV?
You are here: Home / Apple in Education / Bulk Find and Replace By Columns in Excel

Apple in Education / November 6, 2015

Bulk Find and Replace By Columns in Excel

This past week at school, I ran into a problem.  I had a database of student information: Names, ID Numbers, etc.  The only problem is that our barcodes used the wrong Student ID numbers so when we scan the barcodes, the wrong numbers pop up and we can’t search for students by barcode.  For some reason each student has at least three different numbers to identify them: a State ID, Student ID, and Meal Program ID. This was the craziness.

Find and Replace by Columns

My simple solution was that I needed a way to do a Bulk Find and Replace by Columns.  In other words, look for the value of Column F in Column A of the spreadsheet and wherever you find that value, replace it with Column G.

Sounds simple enough right?

My initial Googling didn’t turn up anything because I was searching for something like “Excel Find and Replace by Columns”. After about an hour with no luck and clicking through to a lot of dead ends, I changed my search. “Excel Bulk Find and Replace” And the second result on that page was exactly what I was looking for!

Here’s a link to that original article.

http://www.emreakkas.com/localization-engineering/multiple-find-and-replace-macro-for-excel

Here’s the script that worked for me.


Sub multiFindandReplace()
Dim myList, myRange
Set myList = Sheets("sheet1").Range("D1:E11") 'confirm the sheet name then use two column range with find/replace pairs
Set myRange = Sheets("sheet1").Range("B1:B99") 'range to be searched and replace
For Each cel In myList.Columns(1).Cells
myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlWhole
Next cel
End Sub

Be careful when pasting code as the first time I copied and pasted this code, the quote marks were upgraded which rendered errors in the script.

I hope this code helps someone else who needs to do a bulk find and replace by columns.

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)

Related

Filed Under: Apple in Education

Copyright © 2023 · Digital Pro on Genesis Framework · WordPress · Log in

  • Productivity & Apple Tips
  • Apple in Education
  • Join 8700+ Online Students at Udemy
  • About