+ Reply to Thread
Results 1 to 11 of 11

Find value from one cell and replace to another

  1. #1
    Registered User
    Join Date
    11-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    6

    Find value from one cell and replace to another

    (Moved from this thread.)

    Here's my deal.

    I have a list of data in column A.
    I have a repeated list of text in the corresponding column B.
    **In the text, like the OP, I have set a marker for easy find and replace - INSERTURL**

    I need to copy the contents of A1, and use it in the find (INSERTURL) and replace in B1 (with contents of A1).

    And THEN....recurse to the bottom of the list in A1.

    This seems easy enough.....is it (anxiously twiddling thumbs)


    Moderator's Note: This post was removed from someone else's thread and posted in a thread of it's own. Please read the Forum Rules if you have not done so already, you are expected to abide them. Thanks.
    Last edited by JBeaucaire; 11-27-2012 at 12:42 PM. Reason: Moved post to thread of it's own as per Forum Rules.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find value from one cell and replace to another

    Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix. Use BEFORE/AFTER sheets if that helps make it clearer.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    11-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    6

    Cool Re: Find value from one cell and replace to another

    Note that this was saved in 97-04 but I am using 2011 on a mac. I have windows running on another system, but basically, as you will see here, there is a list of 'names' in column A (380).

    In column B, there is a repeated snippet of code matching the number of populated cells in column A.

    What i need to do is get the value in cell A1, and do a find and replace in B1 for the term INSERTURL.

    So, that would take the value 'bkmrk_LG.flv' from A1, and replace INSERTURL in B1 with the term 'bkmrk_LG.flv'. Now that in and of iteself isn't the big issue. I could do that with any find and replace.

    The issue here is that I am looking for a process that will move down the line doing this function over and over to the end of the populated cells (386 or whatever the last ell with information is).

    I hope I have made some sense of this.

    ....(fingers crossed)

    But THANK you kindly for even reading this and considering the solution...

    Akiba
    Attached Files Attached Files
    Last edited by akibahoward; 11-28-2012 at 05:42 PM. Reason: changing attachment

  4. #4
    Registered User
    Join Date
    11-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    6

    Re: Find value from one cell and replace to another

    Alright..I must first apologize, I HATE when people post, then post again, and again before an answer is given.

    I am ONLY doing this because I think I have simplified the process a bit and hope that this may shed some light on the situation and solution.

    I have reversed the data. Now, the snippet is in column A and the 'replace' names are in column B.

    Now, this would seem to make the logic of my needs;

    If cell A1 has value of X (INSERTURL), replace X with value of B1 > repeat on line 2, then 3, etc.

    That seems a LOT simpler than A to B as I had it before....I have attached the REVERSED and corrected xls that has snippet in column A and names in column B.

    Thanks
    Last edited by akibahoward; 11-28-2012 at 05:41 PM. Reason: Discovered new possibility of solution

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find value from one cell and replace to another

    A sample data sheet doesn't have to be 400 rows long. Usually 10-20 suffice.

    In the sample, you show us the BEFORE and the AFTER so we can see your logic in action. Mock it up manually, then it's easy for us to discuss options/ideas.

  6. #6
    Registered User
    Join Date
    11-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    6

    Re: Find value from one cell and replace to another

    Got it, I wil do the mock up now. Apologies for the huge file....

  7. #7
    Registered User
    Join Date
    11-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    6

    Thumbs up Re: Find value from one cell and replace to another

    Ok, so here are two files. the smpl file is the original with column B with the snippets and column with the 'URL'.

    The Mock Up has the 'URL' from column B replaced in column A.

    So, the premise here is that I would like to do a looping If function where the process searches for iterations of INSERTURL in cell A1 and replaces INSERTURL with the actual URL in cell B1 (there are two iterations of INSERTURL so it needs to do a replace all)then moves to line 2 and repeats with the info ONLY on line two (find all INSERTURL in A2 and replace with URL in B2) etc...

    Thank you SO kindly for your time on this. I TRULY appreciate it!

    Akiba
    Attached Files Attached Files

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find value from one cell and replace to another

    1) put this formula in C1:

    =SUBSTITUTE(A1, "INSERTURL", B1)

    2) Double-click the lower-right corner of C1 and the formula will be copied down column C to the end of the data set

    3) Copy column C

    4) Click on A1 and select Paste Special > Values

    5) Clear column C

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find value from one cell and replace to another

    Same thing in macro form:

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    11-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    6

    Re: Find value from one cell and replace to another

    HOLY CHRISTMAS BATMAN!!!!!!

    The Macro worked PERFECTLY! .004 seconds. The function didn't do so well, but no worries, the macros is just fine.

    Thank you SOOOOO kindly, and YOU ARE THE MAN!!!!

    I will FIND a way to express my thanks beyond just giving you a 'woo hoo' on this forum. Bless you sir.

    Akiba

  11. #11
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find value from one cell and replace to another

    If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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