Need a formula to say if a cell contains certain characters

danniemcq

OK so I have a spreadsheet that I use to check how far along projects are and how close they are to completion.

What I have done is create a checking workbook where I can paste the information from the data spreadsheet run it through a few calculations and have an output tab with the data in easy to see columns that are easy to search through.

What I’m trying to do at the minute is have an if statement if the project filename has “EG” in it will have a true false result in the adjacent cell.

Below is an example of what I’d need
A1

---

B1
55555 – XXX EG XXXXXX XXXXX YES
55555 – XXXX CO XXX XXX XXXX NO
55555XXXX – XXX XXX XXXX NO
55555- XXXXX CO EG XX XXXX YES

You can see that our filenames can be very different I can’t use for example right or left functions to count in a certain amount of characters to find where the EG should be and work it from there.

I was looking at search and find functions but the only way I can see to use that properly is by using the formula =find(“EG”,A1) and if the cell is missing the EG it’ll come back with a #VALUE! Error which I can then use with an ISERROR to give the desired effect. This is a bit of a long winded way to do it as I’m sure there is a much simpler way but I’m stumped.

Any ideas?

amen

Look at the string functions such as InStr

mid

danniemcq wrote: »

OK so I have a spreadsheet that I use to check how far along projects are and how close they are to completion.

What I have done is create a checking workbook where I can paste the information from the data spreadsheet run it through a few calculations and have an output tab with the data in easy to see columns that are easy to search through.

What I’m trying to do at the minute is have an if statement if the project filename has “EG” in it will have a true false result in the adjacent cell.

Below is an example of what I’d need
A1

---

B1
55555 – XXX EG XXXXXX XXXXX YES
55555 – XXXX CO XXX XXX XXXX NO
55555XXXX – XXX XXX XXXX NO
55555- XXXXX CO EG XX XXXX YES

You can see that our filenames can be very different I can’t use for example right or left functions to count in a certain amount of characters to find where the EG should be and work it from there.

I was looking at search and find functions but the only way I can see to use that properly is by using the formula =find(“EG”,A1) and if the cell is missing the EG it’ll come back with a #VALUE! Error which I can then use with an ISERROR to give the desired effect. This is a bit of a long winded way to do it as I’m sure there is a much simpler way but I’m stumped.

Any ideas?

the formula you suggested yourself seems fine, dont see why you need to shorten it as you only need to type it once.

=ISERROR(FIND("EG",A1,1))


maybe convert the original text in A1 to uppercase first before searching for 'EG':

=ISERROR(FIND("EG",UPPER(A1),1))