highlights data if not in 1 decimal

[sksanjeev786]

Hi Team

I have huge data and in the below data some of the data don't have decimals (6.0 refer to cell B14) and the same for others so i wanted to highlight if the data are not in 1 decimal

can anyone help me on this if

Book1
	A	B	C	D			G	H	I	J
2	55	-4.2	52	-0.1			43	5	34	10.3
3	44	-5.5	38	0.2			28	6.8	25	4.4
4	51	-2.2	50	0.1			42	3.4	27	11.9 r
5	48	-2.8	40	2			37	0.1	32	2.7
6	38	2.1	34	4.1			42	-6.9	22	9.4
7	37	7.0 r	43	3.1			37	7.4	29	9.2
8	54	-1.2	53	0.6			54	-2.2	30	12.7 r
9	27	9.0 p	28	7.7 p			47	-14.0 q	25	8.5
10	34	8.3 p	41	2.3			36	1.3	30	5.7
11	16	9.1 p	23	1.6			14	9.4 r	10	11.5
12	55	-1.1	47	3.6			34	5.6	36	1.2
13	28	9.4 p	27	6.7 p			17	9.3 r	13	13.2
14	40	6	37	5.1			40	-4.3	34	0
Sheet4

 

[Joe4]

Are you values really entered as numbers, or are they entered as text?
If you aren't sure, find any blank cell and enter this formula:

=ISNUMBER(B14)

Does that return True or False?

Note I see you have some entries like "9.4 p".
Are those text entries, or numeric with custom formats?

 

[awoohaw]

why are columns A, C, G, I not highlighted those cells do not have a decimal?
are the values in each cell of all columns text cells?

 

[sksanjeev786]

Are you values really entered as numbers, or are they entered as text?
If you aren't sure, find any blank cell and enter this formula:

=ISNUMBER(B14)

Does that return True or False?

Note I see you have some entries like "9.4 p".
Are those text entries, or numeric with custom formats?

Hi Joe,

I wanted to highlight if numbers are not in Decimals shown above in yellow for now i am checking manually where ever i have missed adding 0 manually

Regards
Sanjeev

 

[sksanjeev786]

why are columns A, C, G, I not highlighted those cells do not have a decimal?
are the values in each cell of all columns text cells?

Hi Sir,

Those are the respondent score next column are my delta scores so from PPT i am copying to Excel and after that wherever we have 1.0 is changing to 1 only so i wanted to highlighted those thingns

 

[awoohaw]

Hi Sir,

Those are the respondent score next column are my delta scores so from PPT i am copying to Excel and after that wherever we have 1.0 is changing to 1 only so i wanted to highlighted those thingns

so, what is your objective? To convert the values with p's and r's to a numeric value or to convert the numbers with no decimal to something like #.0?

 

[sksanjeev786]

so, what is your objective? To convert the values with p's and r's to a numeric value or to convert the numbers with no decimal to something like #.0?

Hello Sir,


I just wanted to know in columns B,D, H, J if the number doesn't have 6.0 or not for now B14 has only 6 so wanted to highlight that number

Regards
Sanjeev

 

[awoohaw]

Are you concerned about the numeric format of the number 6? or that the 6 is text? and do you want it converted to a text string?

 

[awoohaw]

if you are looking for text when you want numbers. your conditional format rule would be:

=ISTEXT(B14)

 

[Joe4]

Hi Joe,

I wanted to highlight if numbers are not in Decimals shown above in yellow for now i am checking manually where ever i have missed adding 0 manually

Regards
Sanjeev

I understand that. Please answer the questions I asked in my original reply.
It makes a BIG difference whether these entries are entered as "valid numbers" or "numbers entered as text".