fixing numbers stored as text

[palaeontology]

I have a column of values (all numbers, no text) that were copied from a statistics website and pasted into excel ... column E in the table below .... but it seems they've been pasted as text ... when I use the '=isnumber' formula to check, it comes up False, but '=istext' comes up true.

numbers as text issue.xlsx
	C	D	E	F
1			Factor A	Life Expectancy
2	Argentina		68.40	77.17
3	Australia		89.80	83.94
4	Austria		88.20	82.05
5	Bangladesh		51.70	73.57
6	Belgium		87.90	82.17
7	Bosnia and Herzegovina		78.20	77.93
8	Bulgaria		71.40	75.49
9	Canada		87.60	82.96
10	Chile		76.00	80.74
11	China		74.20	77.47
12	Colombia		67.80	77.87
13	Croatia		81.60	79.02
14	Denmark		85.70	81.4
15	Estonia		81.40	79.18
16	Finland		89.60	82.48
17	France		87.90	83.13
18	Georgia		62.10	74.24
19	Germany		86.40	81.88
20	Greece		87.00	82.8
21	Hungary		79.60	77.31
22	Iceland		93.60	83.52
23	India		44.80	70.42
24	Iran		71.10	77.33
25	Ireland		88.40	82.81
26	Israel		85.50	83.49
27	Italy		88.70	84.01
28	Japan		89.00	85.03
29	Kenya		48.70	67.47
30	Latvia		77.70	75.73
31	Lebanon		80.00	79.27
32	Lithuania		76.60	76.41
33	Mexico		62.60	75.41
34	Netherlands		89.50	82.78
35	New Zealand		86.20	82.8
36	Norway		90.50	82.94
37	Pakistan		43.10	67.79
38	Panama		64.40	79.1
39	Poland		79.60	79.27
40	Qatar		85.20	80.73
41	Romania		74.40	76.5
42	Russia		71.70	72.99
43	Serbia		75.40	76.47
44	Slovakia		78.60	78
45	Slovenia		87.40	81.85
46	South Africa		52.00	64.88
47	South Korea		85.80	83.5
48	Spain		89.60	83.99
49	Sri Lanka		72.80	77.56
50	Sweden		90.50	83.33
51	Switzerland		91.80	84.25
52	Ukraine		72.70	72.5
53	United Arab Emirates		72.20	78.46
54	United Kingdom		84.60	81.77
55	United States		81.30	79.11
56	Vietnam		66.30	75.77
Sheet1

I've tried a range of methods recommended by various excel forums to convert the values back to number format, but each has been unsuccessful.

I've tried ..

* copying and pasting as values
* using '=value(cell reference)'
* Text-to-Column
* changing cell format to 'General'

I can't use the error message method because my excel isn't popping up the error message, despite me ticking the checkbox in options to have errors indicated

Is there something else I can try ?

Kind regards,

Chris

 

[Rick Rothstein]

Try selecting the entire column, calling up the Text To Columns dialog box and clicking the Finish button as soon as it appears. As long as those numbers don't have spaces or non-breaking spaces attached to them, your columns should be all numbers now.

 

[palaeontology]

Hi Rick, that was one of the things I had tried (it was in my list above).

I tried it again just then, but still no change.

As for the spaces or non-breaking spaces that you mentioned, I have my numbers in the table (see my original post). I can't see any spaces in my table.

 

[DRANDON]

=trim() followed by =value() in empty column next to it..

 

[Fluff]

From the alignment it looks as though there is something after the number, what does this return

=CODE(RIGHT(E2))

 

[palaeontology]

=trim() followed by =value() in empty column next to it..

Thanks Drandon. Just tried your suggestion, but after entering the 'value' function, I received a '#Value' error message

Out of curiosity, I just tried the Len function on my two columns .... column E (the one displaying numbers as text) reports that the displayed 68.40 is 6 characters long, whereas my other column that's performing correctly is displaying 77.17 as 5 characters long.

I've just used the 'Left' function to trim it down to the first 5 characters, and it's all now working correctly

 

[palaeontology]

From the alignment it looks as though there is something after the number, what does this return

=CODE(RIGHT(E2))

Hi Fluff,

it's returning the code 160

knowing that, is there now an easier fix for my issue than what I came up with (using the 'Left' function) ??

Kind regards,

Chris

 

[Fluff]

You could select col E, then Ctrl H, in the find what box type ALT 0160 (using the numeric keypad) leave the "replace with" empty & replace all

 

[Fluff]

Alternatively you could use

Sub Clean160()
   Range("E:E").Replace Chr(160), "", xlPart, , , , False, False
End Sub

 

[Rick Rothstein]

it's returning the code 160

knowing that, is there now an easier fix for my issue than what I came up with (using the 'Left' function) ??

Fluff has given you two answers but I just wanted to let you know that ASCII character 160 is what is known as a "non-breaking space".