Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    5

    Unanswered: Is there a function that can 'find' text within a cell?

    I have several cells in a column with random text in each.
    EG:

    Opening Balance 01.01.06
    Supplier Payment
    Transfer to Euro account X

    I would like to group the cells into similar groups eg all payments together, all opening balances together.

    I need a formula that can look at a cell to see if it contains the word 'opening' or 'payment' or 'Euro' etc

    There are literally hundreds of different entries in the cells so I can't use =IF(cell = "Supplier Payment",....

    I need something like IF(cell contains "Payment",....

    Any ideas? Thanks

  2. #2
    Join Date
    Dec 2005
    Posts
    5
    Found a sort of solution searching the net:

    =IF(ISERROR(FIND("Opening",A1))=FALSE,"Opening","" )&IF(ISERROR(FIND("opening",A1))=FALSE,"opening"," ")&IF(ISERROR(FIND("Transfer",A1))=FALSE,"Transfer ","")&IF(ISERROR(FIND("transfer"...

    Just not 100% safe as it's case sensitive....

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    What about using a non-formula approach? You can use Data > Text to Columns to parse each word into a column. Choose Delimited in first step, then use Space as the delimiter in second step.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Another formula approach is to put each qualifier in row 1. For instance, if your cells are in column B, then in C1 put "payment" (without quotes), and in cell C2 put this formula (which is not case sensitive)

    =SUM(LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),UPPER(C$1),"")))/LEN(C$1)

    Then copy down.

    Now if the key term appears in column B, each cell in column C will have a 1, otherwise 0.

    Then put another key term in D1, and use this formula (you can just drag to the right as far as needed and down as far as needed, the formula automaticlaly adjusts).

    Then you can sort based on Column C, then D, then E, etc.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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