Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Formula to find multiple texts with in a string

  1. #1

    Formula to find multiple texts with in a string



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

    Hi Everyone,

    I'm working on a formula to find multiple occurences of different text strings within a single cell. The problem is with one formula will find one text string, but I dont know how to tell to find the others. I have another one that can find either or, but wont return a blank cell if it cant find anything

    =IF(ISNUMBER(SEARCH("*BLAH-2*",J5)),"BLAH-2","")

    =IF(SUM(COUNTIF(G67,{"*BLAH-2*","*GOOD-3*"}))>0,"BLAH-2","GOOD-3")

    Any assistance will greatly be appreciated

    MZING81

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Can you post a small sample workbook of the input and output that you'd expect to see? Just a few rows of different data so that we can see how the formula needs to work?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Acolyte Canapone's Avatar
    Join Date
    Oct 2011
    Location
    Italy
    Posts
    97
    Articles
    0
    Excel Version
    Excel 2010
    Hi All,

    an attempt using an array formula to be confirmed with control+shift+enter.

    In the example F2:F50 hosts the list of substrings you'd need to check, A2 hosts the first big string.


    Code:
    =IFERROR(INDEX($F$2:$F$50,MATCH(1,SEARCH("*"&$F$2:$F$50&"*",A2),0)),"")


    Hope it's a little help
    Last edited by Canapone; 2012-12-21 at 10:29 AM.

  4. #4
    The strings are about 75 charcters of data with most of the info not being that necessary, I just want to pull out the necessary portions. There are about 14 different text strings that I want to extract. I've been using text to columns, but that has been creating issues with sumproduct formulas that will not account for the data, even though its there.


    Quote Originally Posted by Ken Puls View Post
    Can you post a small sample workbook of the input and output that you'd expect to see? Just a few rows of different data so that we can see how the formula needs to work?

  5. #5
    I just need to really know how to connect muliple text strings within my if(isnumber formula, without using a * or & symbols, because those symbols dont function function as an either or.

  6. #6
    Thank you, but the srting is in a single column. I just need to figure an, either, or with the if(isnumber( formula that I have above.

    Thanks

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Quote Originally Posted by MZING81 View Post
    The strings are about 75 charcters of data with most of the info not being that necessary, I just want to pull out the necessary portions. There are about 14 different text strings that I want to extract. I've been using text to columns, but that has been creating issues with sumproduct formulas that will not account for the data, even though its there.
    And that's exactly why a sample would be helpful to see. I'm not sure that a SUMPRODUCT will get you what you want. It may, but I'm not sure. You could nest a whole bunch fo IF statements, but it's going to be a logistical nightmare to maintain. But if we can see what we're working with, then it will make it a LOT easier to help as we can suggest other methods that may be better. As it is, we can write formulas, but we'll be guessing.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    I would have to create a dummie data to replace the existing personal data, thats alot more work than is necessary. All that I need is what i can use to have look for an additional "text" another Blah-3 along with that Blah-2. Ampersands dont work and neither does a comma or an wild-card. All I need is it to say if I cant find Blah-2 then look for Blah-3 etc....

    =IF(ISNUMBER(SEARCH("*BLAH-2*",J5)),"BLAH-2","")





    Quote Originally Posted by Ken Puls View Post
    And that's exactly why a sample would be helpful to see. I'm not sure that a SUMPRODUCT will get you what you want. It may, but I'm not sure. You could nest a whole bunch fo IF statements, but it's going to be a logistical nightmare to maintain. But if we can see what we're working with, then it will make it a LOT easier to help as we can suggest other methods that may be better. As it is, we can write formulas, but we'll be guessing.

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Alright, whatever. If you can't be bothered to provide sample data, I'm not going to build any to test the function. If it's a waste of your time, then it's certainly a waste of mine.

    The IF function works like this:
    =IF(Test,Value if TRUE, Vale if FALSE)

    So, if this works: =IF(ISNUMBER(SEARCH("*BLAH-2*",J5)),"BLAH-2","")

    Then just nest your second IF in place of the "":

    =IF(ISNUMBER(SEARCH("*BLAH-2*",J5)),"BLAH-2",IF(ISNUMBER(SEARCH("*BLAH-3*",J5)),"BLAH-3","")

    And for the next you continue the process:

    =IF(ISNUMBER(SEARCH("*BLAH-2*",J5)),"BLAH-2",IF(ISNUMBER(SEARCH("*BLAH-3*",J5)),"BLAH-3",IF(ISNUMBER(SEARCH("*BLAH-4*",J5)),"BLAH-4",""))

    And so on...
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    if function.xlsxYour right Ken, I shouldve posted the workbook but I didnt think it was necessary cause I thought it was an easy fix, and it really wasn't. I tried adding the comma between IF statements but that only resulted in an value data error.
    if function.xlsx


    So here's the workbook. Sorry for the hassle.

    Quote Originally Posted by Ken Puls View Post
    Alright, whatever. If you can't be bothered to provide sample data, I'm not going to build any to test the function. If it's a waste of your time, then it's certainly a waste of mine.

    The IF function works like this:
    =IF(Test,Value if TRUE, Vale if FALSE)

    So, if this works: =IF(ISNUMBER(SEARCH("*BLAH-2*",J5)),"BLAH-2","")

    Then just nest your second IF in place of the "":

    =IF(ISNUMBER(SEARCH("*BLAH-2*",J5)),"BLAH-2",IF(ISNUMBER(SEARCH("*BLAH-3*",J5)),"BLAH-3","")

    And for the next you continue the process:

    =IF(ISNUMBER(SEARCH("*BLAH-2*",J5)),"BLAH-2",IF(ISNUMBER(SEARCH("*BLAH-3*",J5)),"BLAH-3",IF(ISNUMBER(SEARCH("*BLAH-4*",J5)),"BLAH-4",""))

    And so on...

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

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