+ Reply to Thread
Results 1 to 5 of 5

Trouble finding specific word in text

Hybrid View

  1. 07-20-2019, 12:27 PM #1
    Bas van den Boomen
    Bas van den Boomen is offline
    Registered User
    Join Date
    07-15-2019
    Location
    Rotterdam
    MS-Off Ver
    Office 365 (version 16.26)
    Posts
    11

    Trouble finding specific word in text

    I am currently working on an excel file and I am stuck with two formulas. Attached you can find a file in which you can find the formulas that are not working as I want, to be marked red (column E and N).

    First, I want the formula in column E to display a "1" if the cell in column C or D contains the text "0-jewel", "1-jewel", ..., "10-jewel". However, it displays a "1" for almost all observations because it counts, for example, "22-jewel" as "2-jewel". I want to overcome this problem, but I don't know exactly how to do this. I think the formula I use for columns E untill L is not the most efficient one either.

    Second, I have a similar problem regarding the formula in column N. Same problem, but now with milimeters (mm) instead of -jewels.

    Can somebody please help me rewrite this to an efficient formula?

    Kind regards,

    Bas van den Boomen
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-20-2019, 12:31 PM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Trouble finding specific word in text

    Use this:

    =IF(OR(ISNUMBER(SEARCH({" 0-jewel";" 1-jewel";" 2-jewel";" 3-jewel";" 4-jewel";" 5-jewel";" 6-jewel";" 7-jewel";" 8-jewel";" 9-jewel";" 10-jewel"}," "&$C2)),(ISNUMBER(SEARCH({" 0-jewel";" 1-jewel";" 2-jewel";" 3-jewel";" 4-jewel";" 5-jewel";" 6-jewel";" 7-jewel";" 8-jewel";" 9-jewel";" 10-jewel"}," "&$D2)))),1,0)

    I added a space before each of the search terms... and a space before C2 & D2. Same approach will work for mm.
    Attached Files Attached Files
    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
    Reply With QuoteRegister To Reply
  3. 07-20-2019, 12:43 PM #3
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,719

    Re: Trouble finding specific word in text

    In E2, wrap this in an isnumber function and then adapt for each column

    =IF(LEFT(D2,FIND("-",D2))<=10,1,0)

    In this formula you are only looking at the number to the left of the hyphen "-" instead of the whole value.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  4. 07-20-2019, 02:11 PM #4
    Bas van den Boomen
    Bas van den Boomen is offline
    Registered User
    Join Date
    07-15-2019
    Location
    Rotterdam
    MS-Off Ver
    Office 365 (version 16.26)
    Posts
    11

    Re: Trouble finding specific word in text

    Thank you Glenn and Alan, problem solved!
    Reply With QuoteRegister To Reply
  5. 07-20-2019, 03:14 PM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Trouble finding specific word in text

    You're welcome.


    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Trouble using macro to automatically send e-mail with specific word in cells
    By BioBCCK in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-15-2018, 10:49 AM
  2. [SOLVED] Trouble looking up a text and bringing back something if a value is >= a specific date
    By MR22 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-26-2018, 12:18 PM
  3. [SOLVED] Macro for finding specific Word and Deleting it
    By Haidar123 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-02-2014, 07:45 AM
  4. having trouble finding text within a text string HELP please
    By benakil in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-23-2013, 06:35 AM
  5. Finding a specific word within cell
    By Vespasian in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 01-26-2013, 05:57 AM
  6. Finding a specific word in a cell and executing an array
    By jgray152 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-10-2010, 07:49 PM
  7. [SOLVED] Finding specific word in column
    By Phil #3 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-28-2005, 04:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1