+ Reply to Thread
Results 1 to 12 of 12

Find a text in a sentence column

  1. #1
    Registered User
    Join Date
    10-01-2021
    Location
    NewOrleans, US
    MS-Off Ver
    2016
    Posts
    1

    Find a text in a sentence column

    Hi,
    I have col A and col B as text fields. In col C I have a query list. I'm trying to find if the text values in both col A and col B exist anywhere in col C and then return a true or a false. Please see the attached sheet.

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,718

    Re: Find a text in a sentence column

    Hello XcelProMax. Welcome to the forum.

    I will need more explanation.

    I do not find that Query in row 2 contains either FACT_TABLE or CORE_TABLE values in row 2. How do you get True in Output D2?
    Dave

  3. #3
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365
    Posts
    963

    Re: Find a text in a sentence column

    From the description provided, the values in A2:B2 are found in the contents of C4. XcelProMax indicated that the trigger for true can be found anywhere in Col. C. C4 meets the requirement, so D2 is correctly labelled TRUE.

    Still trying to figure out a solution. BB soon.


    Pete

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,718

    Re: Find a text in a sentence column

    Ah yes. It seems i struggle understanding plain English statements.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,718

    Re: Find a text in a sentence column

    Try this in D2 and fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Edit: This is better.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 10-01-2021 at 03:29 PM.

  6. #6
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: Find a text in a sentence column

    Dave - I initially read it the same way as you. And just a note for XcelProMax: your formula is an array formula needing CTRL-SHIFT-ENTER with XcelProMax's Excel version.

    Also - I think your formula gives TRUE if just one of the conditions is TRUE. Don't they both need to be TRUE??

    A different try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by GeoffW283; 10-01-2021 at 04:28 PM.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,076

    Re: Find a text in a sentence column

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are new here, I will provide the link for you this time: https://www.mrexcel.com/board/thread...olumn.1183493/.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  8. #8
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016
    Posts
    1,016

    Re: Find a text in a sentence column

    Please try at D2

    =OR(COUNTIF($C$2:$C$4,"*"&A2&"*"),COUNTIF($C$2:$C$4,"*"&B2&"*"))

    Regards.

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,267

    Re: Find a text in a sentence column

    Try this in F2 then copied down

    =SUMPRODUCT(1*(ISNUMBER(SEARCH(A2:B2,$C$2:$C$10))))=2
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  10. #10
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: Find a text in a sentence column

    Not surprisingly the five offered formulas each give the expected results per XcelProMax's one test case.

    I created 3 additional test cases and found that none of the formulas (including my own) match my (possibly erroneous) understanding of the expected outcome. Here's a summary:

    query.png

    The attached workbook is an update to XcelProMax's attachment.
    • I think (hope) I have transcribed the various formulas into Main!D2:I4 correctly.
    • The 4 test cases I ran are on the "Cases" worksheet. The changes I made to col-C for each case are highlighted in red.
    • Cases!D1:J19 (the image above) is a snapshot of the formula results for each case obtained by copying each case in turn from the "Cases" worksheet and pasting to Main!A2:D4

    Hopefully I have done this accurately, let me know if not. Maybe XcelProMax can say that some or all of my extra test cases can never occur in practice and thus be able to choose one or more of the already offered formulas.

    I hope this helps rather than confuses!
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,267

    Re: Find a text in a sentence column

    modified formula

    =SUMPRODUCT(1*(ISNUMBER(SEARCH(A3:B3,$C$2:$C$4))))>=2
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016
    Posts
    1,016

    Re: Find a text in a sentence column

    Maybe I misunderstood OP's request, so I use OR instead AND

    modify formula from
    =OR(COUNTIF($C$2:$C$4,"*"&A2&"*"),COUNTIF($C$2:$C$4,"*"&B2&"*"))
    to
    =AND(COUNTIF($C$2:$C$4,"*"&A2&"*"),COUNTIF($C$2:$C$4,"*"&B2&"*"))

    Regards.

+ 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] find text in a sentence
    By maniacs205 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-15-2019, 11:43 PM
  2. Macro to find text in Word and then exctract the full sentence to Excel
    By gratedane8 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-27-2018, 01:56 PM
  3. Replies: 2
    Last Post: 10-25-2013, 02:40 PM
  4. Replies: 1
    Last Post: 06-04-2013, 03:26 PM
  5. [SOLVED] Importing text: each sentence iin one column?
    By KarsdB in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-10-2013, 07:54 AM
  6. Need Help in copying a text/sentence from a column to another ..
    By mike4uuu in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-02-2013, 08:51 AM
  7. Find a specific sentence in a cell full of text
    By BClark86 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-28-2009, 01:16 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.08625 seconds
  • Memory Usage 9,273KB
  • Queries Executed 16 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (12)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (4)postbit_attachment
  • (12)postbit_legacy
  • (12)postbit_onlinestatus
  • (12)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1