+ Reply to Thread
Results 1 to 2 of 2

Formula to Replace Portion of Cell that Contains Matched Text to Cell

  1. #1
    Registered User
    Join Date
    10-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    16

    Formula to Replace Portion of Cell that Contains Matched Text to Cell

    Hi -

    I am working on standardizing several survey workbooks by replacing old question numbers to a new code. For example, a question that was previous S1 should now be Q1. This would also be only for the portion of the cell that contains S1 >> S1_a should become Q1_a

    In order to do this efficiently I have made a key that outlines old text and new text. Since this is for several workbooks, I am hoping to automate the find and replace. I have attempted to use a replace function in conjunction with isnumber, find, and index match. The challenge is essentially that all examples of replace appear to have specific text, and not matched to a row of text. (So i could replace "ABC" with "EFG" but I don't know how to replace cell A1 with A2.

    Please don't judge the formula! If(isnumber(find(index($I$3:$I$6,Match(C10,,$H$3:$H$6,0)),replace(isnumber(find(index($D$3:$D$6,Match(C10,$C$3:$C$6,0)),C10,1)),1,Index($I$3:$I$6,Match(C10,$H$3:$H$6))))))

    I have attached an example workbook.Example for Replacing through Formula.xlsx

    Where original and desire text represent the output and original text and desired text represent the desired formula input.

    Please let me know if you can help!

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,648

    Re: Formula to Replace Portion of Cell that Contains Matched Text to Cell

    Not sure how did you get Q5_c in D6. But try this to see if this is what you are trying to achieve.

    In D3
    Please Login or Register  to view this content.
    and copy down.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to call a portion of the formula string in a different cell in current cell?
    By keilanf1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-18-2014, 09:34 AM
  2. Replace a portion of Multiple cell labels/designators
    By rhartmanUSC in forum Excel General
    Replies: 8
    Last Post: 03-03-2014, 07:10 PM
  3. Replies: 4
    Last Post: 09-16-2013, 03:47 PM
  4. Replace portion of data in 1 cell, with all contents of different cell & save as CSV?
    By 1lowbowtie in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-17-2011, 11:51 PM
  5. Find & Replace only a portion of a cell
    By wewolfe in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-25-2007, 06:26 PM

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