# How to check if a string contains a certain value which has both text and numbers in on Excel

I was wondering how to find a partial value in a string that contains both text and numbers.

I came across a similar problem in the past and I believe I used ISNUMBER(FIND to find the value. However, this contains both numbers and text as opposed to just a number.

I want to find a value within a Pivot Table and put the result in a seperate table.

The exact values I want are under the column 'Path' and want to find any value that contains either: 'Phase 1', 'Phase 2', 'Phase 3', 'Phase 4', 'Phase 5' as well as: 'Active', 'Proposed' and '1 - Critical'.

I will attach an example of the Pivot Table below.

``````| Severity     | Path                     | State    |
|--------------|--------------------------|----------|
| 1 - Critical | \Release 1\Phase 3\Test\ | Active   |
| 2 - High     | \Release 1\Phase 2\      | Proposed |
| 3 - Medium   | \Release 1\Phase 6.3\    | Resolved |
``````

I believe this is the formula to search for a numerical value:

``````=SUMPRODUCT((Pivot_Data[State]={"Active","Proposed"})*(ISNUMBER(FIND("2",Pivot_Data[Severity])))
``````

Can you try using the below:

``````=IF(SUM(IFERROR(FIND({"Phase 1","sdf"},A1),0))>0.5,"found","not found")
``````

Change A1, and add the rest of the list in between the curly brackets `{"Phase 1","Phase 2"}`

And Enter as an array formula, otherwise it will only look for the first word in the list.

• It appears this works, the formula recognises the individual phases instead of including other strings, which is where I was struggling. Thanks a lot! – JRiggers Nov 8 '17 at 16:51

When searching for multiple items, you can simplify the formula by listing the search items and specifying that range in FIND() or SEARCH(): Exceljet:Cell Contains One of Many Things.

This (non-array) formula filled down from B7 gives the results in the table below:

`=IF(SUMPRODUCT(1*ISNUMBER(FIND(A\$7:A\$11,B2)))>0,B2,"")` 