Nth character a number?

[D_N_L]

Hi all,

I'm missing something obvious here but I can't see it. In A2 I have HE123 (formatted as 'General'). I am trying to test if the 3rd character is a number. For some reason the formula I have written returns FALSE.

Please Login or Register  to view this content.

[zbor]

RIGHT function will return result as text. You need to convert it to number.
Try:

Please Login or Register  to view this content.

[zbor]

RIGHT function will return result as text. You need to convert it to number.
Try:

Please Login or Register  to view this content.

[Andy Pope]

Why not use MID?

=ISNUMBER(--MID(A2,3,1))

[D_N_L]

Hi both,

Thanks for that both!

@Andy - could you explain the use of '--' please?

[Kaper]

-- is one of ways to get text converted to numeric value. first minus negates it (if is "3" then becomes -3 and second negates again an we finish with 3.
--yournumbertext is shorter in writing than VALUE(yournumbertext)

Just a side comment.
If we do -- etc on non-number we will get #Arg error. Which of course is not number, so ISNUMBER works fine.
But if one would like not just check if it is number but also what number something shall be done about this error.
for instance
=IFERROR(--MID(A2,3,1),"")
etc.

[Andy Pope]

http://mcgimpsey.com/excel/formulae/doubleneg.html

[D_N_L]

Hi Andy, Hi Kaper - thank you for the explanations. Sorry for the delay in responding.