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

 

[awoohaw]

Okay. Since it is hard for us to know what you want, can you do this:
Please format the entire section as number with at least one decimal place.
Then use this conditional formula is B2, then copy format all through the subject columns.

=IF(ISTEXT(B14),ISNUMBER(FIND(".",B14)),FALSE)

 

[awoohaw]

Okay. Since it is hard for us to know what you want, can you do this:
Please format the entire section as number with at least one decimal place.
Then use this conditional formula is B2, then copy format all through the subject columns.

=IF(ISTEXT(B14),ISNUMBER(FIND(".",B14)),FALSE)

that gets the p's and r's as well. im fine tuning. bear with me.

 

[sksanjeev786]

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?

those are numbers and i wanted to highlight the number if it doest have 6.0

 

[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?

value are in the numbers not in text

 

[sksanjeev786]

that gets the p's and r's as well. im fine tuning. bear with me.t

This is showing me true or false but i need highlited on numbers which doest have 1 decimal

 

[awoohaw]

So, you are asking to see if the cell is formatted with decimal? I"d don't think you can conditionally format based upon cell formatting.

What happens when you format the cell as NUMBER with 1 decimal?

Or are you looking to ensure you've entered a decimal and the 1st number to the right? There is no way I know of to look for a zero in the tenths position when there are no significant digits further to the right.

Help us with your ultimate objective? You say you copy from a PDF, are you just trying to standardize the appearance of the column?

 

[sksanjeev786]

So, you are asking to see if the cell is formatted with decimal? I"d don't think you can conditionally format based upon cell formatting.

What happens when you format the cell as NUMBER with 1 decimal?

Or are you looking to ensure you've entered a decimal and the 1st number to the right? There is no way I know of to look for a zero in the tenths position when there are no significant digits further to the right.

Help us with your ultimate objective? You say you copy from a PDF, are you just trying to standardize the appearance of the column?

Hi Sir,

I have copied it from Powerpoint (PPT) can we have a macro for this ?
For now i need to check manually for each number

 

[awoohaw]

This will give you all numbers that have no fractional portion.

=IF(ISNUMBER(B14),IF(MOD(B14,1)=0,TRUE,FALSE),FALSE)

 

[awoohaw]

Mr Excel 10.xlsm
	A	B	C	D	E	F	G	H	I	J
1
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
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:J14	Expression	=IF(ISNUMBER(J2),IF(MOD(J2,1)=0,TRUE,FALSE),FALSE)	text	NO
H2:H14	Expression	=IF(ISNUMBER(H2),IF(MOD(H2,1)=0,TRUE,FALSE),FALSE)	text	NO
D2:D14	Expression	=IF(ISNUMBER(D2),IF(MOD(D2,1)=0,TRUE,FALSE),FALSE)	text	NO
B2:B14	Expression	=IF(ISNUMBER(B2),IF(MOD(B2,1)=0,TRUE,FALSE),FALSE)	text	NO

 

[awoohaw]

Hi Sir,

I have copied it from Powerpoint (PPT) can we have a macro for this ?
For now i need to check manually for each number

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?