Mid function question.

[JayLee]

In my worksheet, I have:
[Y2]
=MID(D2,FIND("@",D2),7)

Sometimes it returns "#VALUE". I want it to look in another column if that comes up. I would think to use an IF formula to do this. But if I do:
[Z2]
=if(Y2="#VALUE", G2, "")

Unfortunately that doesn't work. How can I just use an IF formula on the MID function to catch the error? Thanks in advance!!!

 

[Aladin Akyurek]

On 2002-10-07 16:39, JayLee wrote:
In my worksheet, I have:
[Y2]
=MID(D2,FIND("@",D2),7)

Sometimes it returns "#VALUE". I want it to look in another column if that comes up. I would think to use an IF formula to do this. But if I do:
[Z2]
=if(Y2="#VALUE", G2, "")

Unfortunately that doesn't work. How can I just use an IF formula on the MID function to catch the error? Thanks in advance!!!

=IF(ISNUMBER(FIND("@",D2)),MID(D2,FIND("@",D2),7),MID(G2,FIND("@",G2),7))

Or...

=IF(ISNUMBER(FIND("@",D2)),MID(D2,FIND("@",D2),7),IF(ISNUMBER(FIND("@",G2)),MID(G2,FIND("@",G2),7),"Not Found"))

 

[JayLee]

Thanks!!!

 

[JayLee]

How's the ISNUMBER function really work?

I'm thinking of using it to:
=abs(P2)

If there is no number, how can i use the ISNUMBER function to display "" in current cell?

Thanks for all your help!

 

[Aladin Akyurek]

On 2002-10-07 17:33, JayLee wrote:
How's the ISNUMBER function really work?

I'm thinking of using it to:
=abs(P2)

If there is no number, how can i use the ISNUMBER function to display "" in current cell?

Thanks for all your help!

=ISNUMBER(P2)

returns TRUE if P2 houses a number like -7, 0, 23.2, or 4, otherwise FALSE.


=IF(ISNUMBER(P2),ABS(P2),"")
This message was edited by Aladin Akyurek on 2002-10-07 17:42

 

[JayLee]

Thanks for helping me understand this!

 

[Mark W.]

Here's another approach...

=IF(LEN(D2)<FIND("@",D2&"@"),G2,MID(D2,FIND("@",D2),7))

...and, if you were to enter the formula, =FIND("@",D2&"@"), into another cell (e.g., E2) as an intermediate result the formula above could be further simplified to...

=IF(LEN(D2)<E2,G2,MID(D2,E2,7))
This message was edited by Mark W. on 2002-10-07 22:04