Formatting numbers as text but come up as false with istext

[Ramballah]

Hello everyone,

I have a few columns where cells will be formatted as followed:

Some cells have letters in them and some dont. However due to this all the cells formatting is plain text. So if i use =ISTEXT then even the cells with just numbers will come up as true.
I need to mark down the cells that contain letters but by using ISTEXT, the ones without letters will also get marked. I tried multiple ways to use custom formatting but I just couldn't find any results.
Google bare no fruits either. So hopefully someone can help me out.
I should mention this is currently in google spreadsheets but I can also convert it to Excel 360.

Thanks in advance,
Ram

 

[JimM]

Does this help?

=IF(SUM(IFERROR(FIND(CHAR(SEQUENCE(58)+64),A1),0))>0,"TRUE","FALSE")

 

[Fluff]

How about

+Fluff 1.xlsm
	M	N
1
2	K 5	TRUE
3	6 5 8	FALSE
4	10 2 J	TRUE
5	9 7 8	FALSE
Main

Cell Formulas
Range	Formula
N2:N5	N2	=ISERROR(SUBSTITUTE(M2," ","")*1)

 

[footoo]

.

 

[Ramballah]

I reckon this would work but sadly the moment I just enter '3' numbers it converts it automatically into a date. So if I enter 5 8 9 it will turn into 2005 8 9 which becomes a problem. So is this possible with just formatting? The only reason I currently have 3 numbers properly shown is because the formatting is plain text. I might also just be dumb right now.

 

[Fluff]

If you want the numbers to stay as just 3 numbers format the cell as text, or put an ' before the first number

 

[Ramballah]

How about

+Fluff 1.xlsm
	M	N
1
2	K 5	TRUE
3	6 5 8	FALSE
4	10 2 J	TRUE
5	9 7 8	FALSE
Main

Cell Formulas
Range	Formula
N2:N5	N2	=ISERROR(SUBSTITUTE(M2," ","")*1)

I tried this formula, however it seems that something goes wrong. I edited the range to fit my column.
Cell i9 and i10 are 3 numbers as followed: 6 5 8, 9 7 8. But only cell i10 is noted as FALSE while i9 is true. For the rest it does say TRUE on all the cells that do have letters in them.
*EDIT* After selecting a different range it did work properly... it might just be sell i9 that is doing it.

 

[Ramballah]

I tried this formula, however it seems that something goes wrong. I edited the range to fit my column.
Cell i9 and i10 are 3 numbers as followed: 6 5 8, 9 7 8. But only cell i10 is noted as FALSE while i9 is true. For the rest it does say TRUE on all the cells that do have letters in them.
*EDIT* After selecting a different range it did work properly... it might just be sell i9 that is doing it.

Conditional formatting left me with this:

While if I just have the formula in the column next to it, it will be correct

 

[Fluff]

Are you saying this is sorted?

 

[Ramballah]

Are you saying this is sorted?

the formula works when I put them in cells, but in conditional format it doesnt work properly. Only the cells with a letter in there should turn green? Maybe I did something wrong. I selected column I and then custom formula and posted this: =ISERROR(SUBSTITUTE($I2;" ";"")*1) However this seemed to give me that result shown in the previous reply.