Results 1 to 4 of 4

Thread: Search string query

  1. #1
    Neophyte Xephon12's Avatar
    Join Date
    Nov 2018
    Posts
    2
    Articles
    0
    Excel Version
    2010

    Search string query



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I'd be really grateful for any help on this. I've scratched my head for quite awhile on it...

    I've got like 10000 entries of customers and each row entry has a free text box which occasionally states in some form "1" meaning their first transaction, I'm wanting to highlight just them rows to myself. It's muddied because the same box also occasionally contains the date like 11/7.

    So, I'm wanting to search a column full of free text to find if it contains the number 1, and only one occurrence of the number 1 and to produce the text output "true" if so.

    I've tried using different methods of finding the first occurance of a number, but don't know how to isolate and verify if that's the number 1.

    Would be so grateful for any help in this matter to save me individually going through all 9000 entries...

    Thanks!

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Maybe something like this?

    =IFERROR(B2+0=1,FALSE)


    where B2 contains the string to check.


  3. #3
    Neophyte Xephon12's Avatar
    Join Date
    Nov 2018
    Posts
    2
    Articles
    0
    Excel Version
    2010
    Sorry, to clarify the free text box has letters as well as numbers in it. I can appreciate that method if I could isolate the number in it, it's usually the very first number....

    I know know how to find the position in the string of the first number, but not how to output what that number actually is to utilize the method you've suggested.

    As an example my free text box would contain data such as:

    Luc 1 right 11/1
    Lea 2 left done
    Right Luc 1 21/2
    Etc

    With me only interested in the first entry.
    Last edited by Xephon12; 2018-11-14 at 06:47 PM.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =ISNUMBER(FIND(" 1 "," "&A2&" "))


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •