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

1. Registered User
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. Registered User
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. Registered User
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.

4. Registered User
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.

#### Posting Permissions

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