+ Reply to Thread
Results 1 to 4 of 4

lookup and replace in spreadsheet

  1. #1
    Registered User
    Join Date
    10-08-2019
    Location
    OH
    MS-Off Ver
    Office 365 ProPlus
    Posts
    2

    lookup and replace in spreadsheet

    I have a spreadsheet which will routinely contain text that I would like to automatically replace, but there are so many that a manual find/replace is no time savings. What I am trying to accomplish is to call some sort of lookup from Excel that will replace "text A" with "text B" every time it sees it, but then go down the list and also replace "text C" with "text D" and so on. I have about 160 pieces of text that will be replaced with new text. The replacements never change - text A will always be replaced with text B; it is pre-defined.

    For example, I have a cell with the paragraph: "Jeff and Sandy have 45 apples from the grocery store."
    and I want to call a function that will always replace every instance of "Jeff" with "Jeffrey", "Sandy" with "Sandra" and "grocery store" with "Kroger".

    I could literally write a macro of doing a find and replace for every 160 pieces of text, but I would rather have some sort of lookup. Whether in an Access DB or another array of cells; I'm just not sure what the best route is or where to start. I have some shell scripting knowledge and if this were Linux I would just create a flat file and reference it on a for loop - I'm kind of looking for a way to do the same thing somewhere in Office.

    One issue is also that I am limited on what I am permitted to do as a non-administrative user and also have very limited vbscript experience. Ideally I'd like it to be sophisticated enough to work well and save us time, but simple enough that anyone with a little training could support it.

    I've be grateful for some ideas or guidance on best routes to accomplish this!
    Last edited by Shae1999; 10-08-2019 at 02:13 PM.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    6,630

    Re: lookup and replace in spreadsheet

    Hi.

    This is easily done.

    A simple Macro will do what you need.

    The question is where to put the macro.


    1. It can be in your personal workbook. This means that the macro will be available whenever excel is open.
    You can use a shortcut to run the macro. However to prevent the Macro being run inadvertently you would need
    Some method of checking that the right spreadsheet was being modified. ie Check spreadsheet name or cell contents.

    2. It can be in your spreadsheet. Not so good if the spreadsheet is sent to you or created by another application.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  3. #3
    Registered User
    Join Date
    10-08-2019
    Location
    OH
    MS-Off Ver
    Office 365 ProPlus
    Posts
    2

    Re: lookup and replace in spreadsheet

    Thanks for this! As I mentioned, I am aware I can use find/replace in a macro, but I have a list of 160 replacements, so this would be highly manual to create. I was hoping for something a bit more sophisticated. Do you have a suggestion for what I would use in this macro other than doing 160 manually entered find/replacements?

    Thanks!

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    6,630

    Re: lookup and replace in spreadsheet

    Ok.

    You are not understanding the questions.

    So I have created something to get you started.

    Create a sheet in your workbook called "Data"

    Starting in A1 enter the search text in column A
    Starting in B1 enter the search text in column B

    Eg:

    Jeff, Jeffrey Jeff followed by a comma
    Jeff Jeffrey Jeff followed by a space
    Sandy Sandra
    grocery store Kroger




    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1