Ranking

[Mjb2]

Hi, I have imported some data from an online database and when I try to rank the values in that column it doesn't work.
If I then type in those same values into my spreadsheet in another column I am able to rank them.
Does anyone know why this is ?

Cheers
mjb

 

[Toadstool]

Hi Mjb2,

The most common reason for having challenges with data downloaded from websites is the presence of Non-Breaking Spaces, often represented as &nbsp and sometimes called whitespace, which are used for better visual presentation.

You may also have other characters, such as spaces, preventing your downloaded values from being interpreted as numbers.

Here's one way to clean out whitespaces (characters held as decimal 160). I'm showing the column A which contains some non-breaking spaces, column B fails to rank due to these, column C uses SUBSTITUTE to remove them and column D can now rank the new data in column C.

Book3
	A	B	C	D
1	From Online	RANK	SUBSTITUTE	RANK
2	11	#VALUE!	11	4
3	22 22	#VALUE!	2222	1
4	22	1	22	3
5	9 9	#VALUE!	99	2
6	1	4	1	7
7	1	#VALUE!	1	7
8	2	3	2	6
9	3	2	3	5
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=RANK(A2,$A$2:$A$9)
C2:C9	C2	=SUBSTITUTE(A2,CHAR(160),"")+0
D2:D9	D2	=RANK(C2,$C$2:$C$9)

 

[Mjb2]

Thanks Toadstool, I'll try that later today.

Cheers

 

[Mjb2]

Thanks Toadstool, it worked really well.
Cheers

mjb2

 

[Toadstool]

Thanks Toadstool, it worked really well.
Cheers

mjb2

You're welcome.