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

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....

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.

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.