+ Reply to Thread
Results 1 to 4 of 4

Replace the word in A1 in the sentence in B1 with ( )

  1. #1
    Registered User
    Join Date
    12-01-2020
    Location
    Tokyo
    MS-Off Ver
    Win 10
    Posts
    2

    Replace the word in A1 in the sentence in B1 with ( )

    I have over 5,000 entries I need to do this to....but only 2 examples are attached here.

    Column A has 1 word entries. For example "bear"
    Column B has a sentence using the word from column A. For example "The bear lives in the woods"

    I need to replace the Column A word in the sentence with ( ) brackets.

    The final result in Column B will be
    ( ) lives in the woods.
    The altered sentence can be placed in Column C if that helps.

    The problem I`m having is, that this is all done in Japanese and macros don`t play nice with Japanese characters.
    So I`m looking for another way.

    Would anyone have any thoughts?

    Thank you.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Replace the word in A1 in the sentence in B1 with ( )

    Mmm. The bear lives in the woods. That makes a change from its usual forest-based activity!!

    This can be done by formula, but there is a snag. SUBSTITUTE is case-sensitive. So if bear is capitalised at the beginning of a sentence, it will be treated differently than a non-capitalised bear mid-sentence. It can be done if the entire string is returned as either upper case or lower case... but not (I think) otherwise. See sheet to see the problem.
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 12-01-2020 at 06:32 AM.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Replace the word in A1 in the sentence in B1 with ( )

    Are you still uisng Excel 2003? If not, please update your profile. If not, why are you still using the obsolete .xls file format?

  4. #4
    Registered User
    Join Date
    12-01-2020
    Location
    Tokyo
    MS-Off Ver
    Win 10
    Posts
    2

    Re: Replace the word in A1 in the sentence in B1 with ( )

    Glenn,
    Hilarious. I didn`t notice the bear in the woods reference.

    That solution work.
    Case sensitivity isn`t an issue. THANK YOU !
    As expected it was beyond my current level.

    I`m stuck with an old laptop at the moment that is still Win 7, (which is parent-speak for my son had my laptop and it`s didn`t come back in perfect condition) thus the old version.

    Thank you again for the help.
    It`s greatly appreciated.

    John

+ 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. Replies: 15
    Last Post: 01-30-2018, 09:13 AM
  2. Replies: 4
    Last Post: 03-12-2015, 12:45 PM
  3. Find and replace a new sentence or paragraph in word doc
    By krazyhype19 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-03-2014, 06:06 AM
  4. [SOLVED] Find and Replace Exact Word in Sentence.
    By rr1050 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-03-2014, 01:58 AM
  5. Replace word in a sentence
    By dmarop in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-14-2014, 02:50 PM
  6. [SOLVED] How can I replace a word from a sentence in a cell & substitute word from another cell
    By rionoah in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-11-2012, 08:44 AM
  7. How to find and replace a word in a sentence with VBA
    By Gunilla in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-20-2010, 09:52 PM

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