Use ISTEXT or ISNUMBER with Right Function

[pto160]

When I use the ISNUMBER or ISTEXT function for the whole cell, it gives me the correct true or false result. When I use the ISNUMBER OR ISTEXT with the right function to extract the last 3 characters it is giving me the incorrect results. I want Excel to see the the last 3 characters as if it is the whole cell so it gives me the correct TRUE or FALSE result. So C12052005 is 005 or 5 (the last 3 characters) which is a number. C12052005A is 05A which is text. Here is an example.

Unique List_Frequency.xlsx
	A	B	C
1		ISNUMBER	ISTEXT
2	1	TRUE	FALSE
3	A	FALSE	TRUE
4	B	FALSE	TRUE
5	2	TRUE	FALSE
6
7	C12052005	FALSE	TRUE
8	C12052005A	FALSE	TRUE
9	C12052005B	FALSE	TRUE
10	C12052002	FALSE	TRUE
Sheet3

Cell Formulas
Range	Formula
B2:B5	B2	=ISNUMBER(A2)
C2:C5	C2	=ISTEXT(A2)
B7:B10	B7	=ISNUMBER(RIGHT(A7,3))
C7:C10	C7	=ISTEXT(RIGHT(A7,3))

 

[sandy666]

maybe

Book1
	A	B
7	C12052005	TRUE
8	C12052005A	FALSE
9	C12052005B	FALSE
10	C12052002	TRUE
Sheet2

Cell Formulas
Range	Formula
B7:B10	B7	=ISNUMBER(--RIGHT(A7,3))

 

[pto160]

Great. Thanks. How would you use it if you want to use the ISTEXT function?

 

[sandy666]

ISTEXT is not flexible like ISNUMBER but you can do that

Book1
	A	B	C
6	C12052005	TRUE	FALSE
7	C12052005A	FALSE	TRUE
8	C12052005B	FALSE	TRUE
9	C12052002	TRUE	FALSE
Sheet3

Cell Formulas
Range	Formula
B6:B9	B6	=ISNUMBER(--RIGHT(A6,3))
C6:C9	C6	=ISTEXT(IFERROR(--RIGHT(A6,3),"x"))

 

[pto160]

Great. Thanks. That works. So the right function treats the extracted text as a text string unless you convert it a number.

 

[sandy666]

functions RIGHT, LEFT, MID and some more are text functions so if you want result as number you will need to *1 or +0 or use double --