+ Reply to Thread
Results 1 to 12 of 12

Substitute an expression column B that matches the expression in column A.

  1. #1
    Registered User
    Join Date
    08-18-2020
    Location
    Valencia
    MS-Off Ver
    Mac excel - Office 365 - v 16.72
    Posts
    61

    Substitute an expression column B that matches the expression in column A.

    Hi, I have column A filled with a phrasal verb. I need to find the same expression in column B and replace it with (.............)

    Take care of I had to take care of my daughter because she was ill.
    Take charge of I will take charge of the project and ensure it is completed on time.
    Take drugs It is not safe to take drugs because they can be harmful to your health.
    Take exercise I take exercise every morning to stay healthy and fit.
    Take medicine You should take medicine to relieve your headache.
    Take notes It's important to take notes during class so you can review them later.

    I am on a macbook and everytime I try and paste in a formula on cell A1 for example, it replaces the text. I tried pasting formula only and I couldn't.
    Should I be using a LOOKUP or MATCH function or even a substitute function.

    Thanks

  2. #2
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,647

    Re: Substitute an expression column B that matches the expression in column A.

    Please Login or Register  to view this content.
    Try this formula
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  3. #3
    Registered User
    Join Date
    08-18-2020
    Location
    Valencia
    MS-Off Ver
    Mac excel - Office 365 - v 16.72
    Posts
    61

    Re: Substitute an expression column B that matches the expression in column A.

    Wow that was so fast and yes, it works perfectly.
    Thank you so much - I have thousands of lines.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2503
    Posts
    13,702

    Re: Substitute an expression column B that matches the expression in column A.

    @ codeBeast

    Please update your forum profile.

    Is your forum profile showing the Excel PRODUCT that you need this request to work with?

    The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

    The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.
    Dave

  5. #5
    Registered User
    Join Date
    08-18-2020
    Location
    Valencia
    MS-Off Ver
    Mac excel - Office 365 - v 16.72
    Posts
    61

    Re: Substitute an expression column B that matches the expression in column A.

    Hi.
    MS 365
    Version 16.72

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2503
    Posts
    13,702

    Re: Substitute an expression column B that matches the expression in column A.

    Then please show that in your profile. It helps us help you if it's kept current.

  7. #7
    Registered User
    Join Date
    08-18-2020
    Location
    Valencia
    MS-Off Ver
    Mac excel - Office 365 - v 16.72
    Posts
    61

    Re: Substitute an expression column B that matches the expression in column A.

    Hi - I couldn't see where to put that info so I pressed on "visitor messages" and put it there. Is that right?

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2503
    Posts
    13,702

    Re: Substitute an expression column B that matches the expression in column A.

    No.

    To update your profile find the dark blue menu at the top of this page that says
    Forum Actions
    . Click that and 'Edit Profile'.

    Thank you ahead of time.

  9. #9
    Registered User
    Join Date
    08-18-2020
    Location
    Valencia
    MS-Off Ver
    Mac excel - Office 365 - v 16.72
    Posts
    61

    Re: Substitute an expression column B that matches the expression in column A.

    Hi - sorry to be a pain but I have a little problem.
    The word that I look up in column A is not always exactly the same as the one in column B
    examples

    column A column B column C
    ache My body is going to ache tomorrow. My body is going to (....................) tomorrow.
    repair My car was repaired yesterday. (no match found)

    I need to lookup and substitute formula to include the keyword to look up or keyword+d or keyword+ed or keyword+s or keyword+ing or keyword+es
    These would be the variants.

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2503
    Posts
    13,702

    Re: Substitute an expression column B that matches the expression in column A.

    Sorry but your profile still does not reflect what you posted in your Post#5.
    Please fix that. See my Post#4 for instructions.

    I'm going to need context. For that it is best to upload a small Excel workbook sample of the problem you are working on. Instructions are in the 'gold' banner at the top of the page.

  11. #11
    Registered User
    Join Date
    08-18-2020
    Location
    Valencia
    MS-Off Ver
    Mac excel - Office 365 - v 16.72
    Posts
    61

    Re: Substitute an expression column B that matches the expression in column A.

    Hi - I just changed my profile - thanks.
    Just included the excel.

    It's the second tab - substitute. COlumn C has the formula.
    If you look at the last row - it substitutes the word in column A but leaves the "ed" outside.

    I would like the substitute to look for the keyword and take into consideration the endings that I mentioned above.

    I cannot attach the excel file right now. I will try again - I get an error.

  12. #12
    Registered User
    Join Date
    08-18-2020
    Location
    Valencia
    MS-Off Ver
    Mac excel - Office 365 - v 16.72
    Posts
    61

    Re: Substitute an expression column B that matches the expression in column A.

    Here's the excel. Only one tab now named substitute. Looks like the excel was too big before.
    Attached Files Attached Files

+ 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" [expression.error] the column 'Symbol' was not found
    By steveal in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-18-2020, 12:14 PM
  2. Replies: 4
    Last Post: 08-15-2020, 05:42 PM
  3. [SOLVED] INDEX on a cell in Column H if field $A4 matches Column D *OR* Matches Column E?
    By StevenCranford in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-09-2017, 09:05 PM
  4. VBA Expression to Add all Subtotals in a column when a condition is met otherwise return 0
    By deathtobelievers in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-27-2015, 04:05 PM
  5. Need VBA expression
    By SDruley in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-03-2012, 01:32 AM
  6. [SOLVED] need help with expression
    By Peterpunkin in forum Excel General
    Replies: 5
    Last Post: 05-04-2006, 01:00 PM
  7. Expression
    By gudway in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-04-2006, 08:20 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