1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2007 Mass Find & Replace

Discussion in 'Business Applications' started by en-pro, Dec 26, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. en-pro

    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?
     

    Attached Files:

  2. draceplace

    draceplace

    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.
     

    Attached Files:

  3. en-pro

    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?
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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
     

    Attached Files:

  5. en-pro

    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
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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
     
  7. en-pro

    en-pro Thread Starter

    Joined:
    Dec 26, 2012
    Messages:
    4
    I figured it out! Thank you!!
     
  8. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Great job!! Please mark the thread as solved.

    Rollin
     
  9. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Great job guys! Thanks for picking up for me Rollin, got busy shoveling snow!
     
  10. Rollin_Again

    Rollin_Again

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


    Rollin
     
  11. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1082456

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice