+ Reply to Thread
Results 1 to 5 of 5

Replace the word "bb" in Column B with "cc", if "aa" is found in Column A

  1. #1
    Registered User
    Join Date
    11-20-2021
    Location
    Taiwan
    MS-Off Ver
    Excel for Mac v16.55
    Posts
    2

    Replace the word "bb" in Column B with "cc", if "aa" is found in Column A

    Hi, I am a newbie here. I have two columns with the contents shown below:

    Screen Shot 2021-11-21 at 12.39.15 AM.png

    Now I want to replace the word "bb" in Column B with "cc", if "aa" is found in Column A in the same row. The desired result is shown below:

    Screen Shot 2021-11-21 at 12.39.30 AM.png

    Can this be done with the Excel formulas? Thank you.
    Last edited by isend; 11-20-2021 at 01:28 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Replace the word "bb" in Column B with "cc", if "aa" is found in Column A

    Can this be done with the Excel formulas?
    Yes, but not in the same column. You'd need to test the two columns with a formula in column C.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Replace the word "bb" in Column B with "cc", if "aa" is found in Column A

    Hi,

    A formula can't alter another cell.

    However this is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

    Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

    You may find

    =IFERROR(IF(SEARCH("aa",A1)>0,SUBSTITUTE(B1,"bb","cc"),A1),B1)

    will give you the text required but f the colours are significant and you want part of he text to to be a colour then you may need to have a macro do that.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    11-20-2021
    Location
    Taiwan
    MS-Off Ver
    Excel for Mac v16.55
    Posts
    2

    Re: Replace the word "bb" in Column B with "cc", if "aa" is found in Column A

    Quote Originally Posted by Richard Buttrey View Post
    =IFERROR(IF(SEARCH("aa",A1)>0,SUBSTITUTE(B1,"bb","cc"),A1),B1)
    Thank you Richard. This is exactly what I want!

  5. #5
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Replace the word "bb" in Column B with "cc", if "aa" is found in Column A

    Formula for C1 =IF(OR(ISERROR(FIND("aa",A1)),ISERROR(FIND("bb",B1))),B1,SUBSTITUTE(B1,"bb","cc"))

    Fill down until you reach the bottom of your data.

    Copy column B and pastespecial values over column A

    Delete column C
    Last edited by mehmetcik; 11-20-2021 at 02:01 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

+ 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] Extracting the common values found in Column "A" & Column "B" into Column "C"
    By Asad Mir in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 07-18-2021, 06:36 AM
  2. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  3. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  4. [SOLVED] If there is any text in column "A$" on "sheet1" then move cell to column "A$" on "sheet2"
    By ckgeary in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-27-2013, 08:28 PM
  5. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  6. Replies: 3
    Last Post: 04-14-2013, 11:53 PM
  7. [SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 06:26 AM

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