Solved: Excel 2007 Mass Find & Replace

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

en-pro

Thread Starter
Joined
Dec 26, 2012
Messages
4
I have a list of people and the location numbers they're associated with, but I need to replace those location numbers with the full name of their site. I can find & replace one at a time, but am looking for a way to do them quicker. There are almost 10000 lines of sites to update with the 400 different site names.

I am uploading the site names and the file I'm trying to update (Responder Upload- refer to the Responders tab). Column G needs to be updated with the full location names.

Any ideas?
 

Attachments

Joined
Jun 8, 2001
Messages
2,583
Welcome to TSG en-pro!

I pulled the site names into the the responder spreadsheet. Had to delete some stuff to get it under 200, probably should have left it 2 sheets. but you'll get the gist of it.

Broke the name and number out (may need some manual fixing at bottom of list). I then used the Vlookup function to pull the name to the responder spreadsheet. Vlookup has to have the lookup value in the first column.
 

Attachments

en-pro

Thread Starter
Joined
Dec 26, 2012
Messages
4
Thank you draceplace! What I actually need to do is replace the number with the corresponding name (including the number) ex replace 00307 with 00307 Coit. Could you walk me through that?
 
Joined
Sep 4, 2003
Messages
4,916
To do this you would just concatenate the two other columns in a "helper" column and then copy the entire column and paste into the original column by right clicking and choosing PASTE SPECIAL and then use VALUES as the paste option. Then delete the column(s) that you don't need.

I've done this for you in the modified file that Drace included and have attached it in case you can't figure it out on your own.

Rollin
 

Attachments

en-pro

Thread Starter
Joined
Dec 26, 2012
Messages
4
Concatenate I've got- What I don't know is how to take the V lookup in drace's file and apply it to my actual file. The formula isn't making a lot of sense to me. Thank you both for your help- I think I'll have them all manually "find & replaced" by the time I figure out a "faster" way to do it
 
Joined
Sep 4, 2003
Messages
4,916
So it looks like Drace copied the sheet containing the lookup values to the same workbook that you are try to replace the values in since VLOOKUP can be a little flakey between separate workbooks. Drace then separated the site name and site codes by using either a formula or Excel's "Text to Columns" feature. He/She then re-arranged the columns so that the site code was in the first column and the site name was in the second column. Once it is in this format the standard VLOOKUP formula is applied. If you aren't familiar with VLOOKUP just Google it. The hardest part of the who thing is putting the data in a format that VLOOKUP can work with. What part exactly do you not understand?

Rollin
 
Joined
Sep 4, 2003
Messages
4,916
Come on admit it.....You were out there drinking Eggnog and making snow angels weren't you LOL !!!


Rollin
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top