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]

If you just want to Conditional Format the numbers without decimals in yellow, do the following:

1. Select the range B2:J14
2. Go to Conditional Formatting
3. Select "New Rule"
4. Select "Use a formula to determine which cells to format"
5. Enter the following formula:

=AND(ISNUMBER(B2),B2=INT(B2))

6. Click the Format button
7. Go to the Fill tab and select your yellow color
8. Click OK

 

[sksanjeev786]

You still have not answered what is the objective? What are you going to do with the cell once you have it highlighted? If you are just going to type ".0" after it, I have already suggested you can do that by formatting as number with one decimal place. And you haven't said what is the current formatting of the column? is it general, numeric, or text?

Hello sir,

Well there are 2 scenarios on this Since i have copied this data from Powerpoint and i need to do a Quality check for this data
So if ".0" is missing excel i need to check on PPT and add .0 where ever it got missed on PPT.

Hope you got my answer

Regards
Sanjev

 

[awoohaw]

Hello sir,

Well there are 2 scenarios on this Since i have copied this data from Powerpoint and i need to do a Quality check for this data
So if ".0" is missing excel i need to check on PPT and add .0 where ever it got missed on PPT.

Hope you got my answer

Regards
Sanjev

Have you formatted the cells as number with 1 decimal? and what is the current formatting of the cells?

 

[shinigamilight]

=AND(ERROR.TYPE(SEARCH(".",A2))=3,ISEVEN(COLUMN(A2)))

 

[sksanjeev786]

Have you formatted the cells as number with 1 decimal? and what is the current formatting of the cells?

no sir, I just copied from PPT to Excel and not doing any formatting so that i can get accurate data as per PPT

 

[awoohaw]

=AND(ERROR.TYPE(SEARCH(".",A2))=3,ISEVEN(COLUMN(A2)))

that is is a nifty function! I don't see how it works as if a cell is formatted as #.0 it still resolves to true.

 

[awoohaw]

no sir, I just copied from PPT to Excel and not doing any formatting so that i can get accurate data as per PPT

after copy from power point PLEASE attempt a format as numbers with one decimal?

 

[sksanjeev786]

after copy from power point PLEASE attempt a format as numbers with one decimal?

Yes i have tried sir but in between i some charter but you have provided the Conditional formatting thats work for me

I know thing are getting complicated but from Really Appreciate team effort on this

 

[awoohaw]

i'm happy to know you have the solution.

Best wishes.