Round function is giving me error because they are not numbers.

[lezawang]

Hi

The first 4 values in the sheet below, they look they are numbers (aligned to the right) but Round(number,2) gave me error message. I used Isnumber() and they are not number. I multiple these number by 1 using Copy/PasteSpecial but they are still not number. I highlighted them and Clear Format but still not numbers? What is causing that? Please see the link below to download the file. Thank you very much.

Round.xlsx

drive.google.com

Round.xlsx
	A	B	C	D
3	21,072.20		#VALUE!	FALSE
4	$ 7,902.07		#VALUE!	FALSE
5	$ 2,634.02		#VALUE!	FALSE
6	$ 7,902.07		#VALUE!	FALSE
7	9219.085327		9219.09	TRUE
8	6585.060948		6585.06	TRUE
9	6585.060948		6585.06	TRUE
10	44778.41445		44778.41	TRUE
11	6585.060948		6585.06	TRUE
Sheet1

Cell Formulas
Range	Formula
C3:C11	C3	=ROUND(A3,2)
D3:D11	D3	=ISNUMBER(A3)

 

[Fluff]

What does

=CODE(A3)

return?

 

[lezawang]

160, all of the 4 cells. Please see below, thank you.

Round.xlsx
	A	B	C	D	E
3	21,072.20		#VALUE!	FALSE	160
4	$ 7,902.07		#VALUE!	FALSE	160
5	$ 2,634.02		#VALUE!	FALSE	160
6	$ 7,902.07		#VALUE!	FALSE	160
7	9219.085327		9219.09	TRUE	57
8	6585.060948		6585.06	TRUE	54
9	6585.060948		6585.06	TRUE	54
Sheet1

Cell Formulas
Range	Formula
C3:C9	C3	=ROUND(A3,2)
D3:D9	D3	=ISNUMBER(A3)
E3:E9	E3	=CODE(A3)

 

[Toadstool]

Hi Lezawang,

It appears those cells has some non-breaking space characters (decimal 160, hex A0)

You could use this to convert:

=IF(ISNUMBER(A3),A3,VALUE(SUBSTITUTE(A3,CHAR(160),"")))

 

[Fluff]

As Toadstool has said, they are non-breaking spaces which you will need to remove. You may also need to get rid of the $ signs depending on your settings.

 

[lezawang]

Thank you all it works now. I have one question please, what made you think there is char(160)? non-breaking space? from just looking at the cell? thanks again

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Toadstool]

Thank you all it works now. I have one question please, what made you think there is char(160)? non-breaking space? from just looking at the cell? thanks again

I used the CODE function to check the cell contents. It doesn't work on your XL2BB version but here's the Google Drive download version:

LezWang3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1
2					Result	CODE
3	21,072.20		#VALUE!	FALSE	21072.2	160	160	32	50	49	44	48	55	50	46	50	48
4	$ 7,902.07		#VALUE!	FALSE	7902.07	160	160	36	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	32	55	44	57	48	50	46	48	55
5	$ 2,634.02		#VALUE!	FALSE	2634.02	160	160	36	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	32	50	44	54	51	52	46	48	50
6	$ 7,902.07		#VALUE!	FALSE	7902.07	160	160	36	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	160	32	55	44	57	48	50	46	48	55
7	9219.085327		9219.09	TRUE	9219.085	57	57	50	49	57	46	48	56	53	51	50	55
8	6585.060948		6585.06	TRUE	6585.061	54	54	53	56	53	46	48	54	48	57	52	56
9	6585.060948		6585.06	TRUE	6585.061	54	54	53	56	53	46	48	54	48	57	52	56
10	44778.41445		44778.41	TRUE	44778.41	52	52	52	55	55	56	46	52	49	52	52	53
11	6585.060948		6585.06	TRUE	6585.061	54	54	53	56	53	46	48	54	48	57	52	56
12	14487.13409		14487.13	TRUE	14487.13	49	49	52	52	56	55	46	49	51	52	48	57
13	3951.036569		3951.04	TRUE	3951.037	51	51	57	53	49	46	48	51	54	53	54	57
Sheet1

Cell Formulas
Range	Formula
C3:C13	C3	=ROUND(A3,2)
D3:D13	D3	=ISNUMBER(A3)
E3:E13	E3	=IF(ISNUMBER(A3),A3,VALUE(SUBSTITUTE(A3,CHAR(160),"")))
F3:F13	F3	=CODE(A3)
G3:AR13	G3	=IFERROR(CODE(MID($A3,COLUMN()-COLUMN($F$3),1)),"")

 

[Fluff]

what made you think there is char(160)?

Just an educated guess, because when I copied your mini-sheet everything was fine.

 

[Joe4]

I have one question please, what made you think there is char(160)? non-breaking space?

They are not uncommon in data files that come from:
- the web
- other applications