Nested IF FIND Function #Value!

[Mackey00]

Hi all,

I have been pulling my hair out for the last few hours reworking this nested IF function. I have different values in A and b/c the numbers in front can change from two to three digits I have to use a LEFT/LEN function to remove characters from the right. My embedded IF function first asks if A4 is true then display 0. The second IF asks if PERORDER is in a cell then remove the last 8 characters to display the 35 (A5). Finally, if false then remove the last 6 characters to show 3 (A3).

The function appears to be breaking down on the final rule "the false statement". Thank everyone in advance for reading and replying.

Raw Data (3)

*	B	P
1	Comm	Comm %
2	3 %not	#VALUE!
3	3 %not	#VALUE!
4	ST00	0
5	35PERORDER	35
6	3 %not	#VALUE!

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula P2 =IF(B2="ST00",0,IF(FIND("PERORDER",B2,1),LEFT(B2,LEN(B2)-8),LEFT(B2,LEN(B2)-6))) P3 =IF(B3="ST00",0,IF(FIND("PERORDER",B3,1),LEFT(B3,LEN(B3)-8),LEFT(B3,LEN(B3)-6))) P4 =IF(B4="ST00",0,IF(FIND("PERORDER",B4,1),LEFT(B4,LEN(B4)-8),LEFT(B4,LEN(B4)-6))) P5 =IF(B5="ST00",0,IF(FIND("PERORDER",B5,1),LEFT(B5,LEN(B5)-8),LEFT(B5,LEN(B5)-6))) P6 =IF(B6="ST00",0,IF(FIND("PERORDER",B6,1),LEFT(B6,LEN(B6)-8),LEFT(B6,LEN(B6)-6))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

if false then remove the last 6 characters to show 3 (A3).

Remove last 5?
Try

=IF(B2="ST00",0,IF(ISNUMBER(FIND("PERORDER",B2,1)),LEFT(B2,LEN(B2)-8),LEFT(B2,LEN(B2)-5)))

 

[Mackey00]

Peter,

I just tried it and if I removed the -6 it would show "3 %not", so I put the -6 back in and it worked perfectly. You fixed it by putting the ISNUMBER in the function. Thank you very much!! I don't know if I would have ever noticed that.

 

[Peter_SSs]

Peter,

I just tried it and if I removed the -6 it would show "3 %not", so I put the -6 back in and it worked perfectly. You fixed it by putting the ISNUMBER in the function. Thank you very much!! I don't know if I would have ever noticed that.

I wasn't suggesting removing the -6, but replacing it with -5. If your formula works with -6 then your data must not be like this red highlighted text as that only contains 6 characters altogether. So if you remove 6 there would be nothing left.
I guess there must be another space in there somewhere. Anyway, if you have it working, that's all that matters.

 

[Mackey00]

Peter,

Can you explain how this function now works with the ISNUMBER? I don't understand why it works. It appears the ISNUMBER would return a FALSE, or does it return a TRUE on cell B5?

 

[Mackey00]

Yes, there was a space behind the 3 %not to make the total cell 7 digits.

 

[Peter_SSs]

Peter,

Can you explain how this function now works with the ISNUMBER? I don't understand why it works. It appears the ISNUMBER would return a FALSE, or does it return a TRUE on cell B5?

On your cell B5, the ISNUMBER(FIND( would return True while on B6 it would return False.

The problem with your earlier formula was that for cell B5, the FIND would have returned 3 (being the starting position of "PERORDER" in the cell) which acts the same as True.
However, for cell B6, the FIND would have returned an error (since the text isn't found) and hence your whole formula returned an error.