1

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])))
0

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
1

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,"")

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.