fixing numbers stored as text

palaeontology

Active Member
Joined
May 12, 2017
Messages
444
Office Version
  1. 2016
Platform
  1. Windows
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
CDEF
1Factor ALife Expectancy
2Argentina68.40 77.17
3Australia89.80 83.94
4Austria88.20 82.05
5Bangladesh51.70 73.57
6Belgium87.90 82.17
7Bosnia and Herzegovina78.20 77.93
8Bulgaria71.40 75.49
9Canada87.60 82.96
10Chile76.00 80.74
11China74.20 77.47
12Colombia67.80 77.87
13Croatia81.60 79.02
14Denmark85.70 81.4
15Estonia81.40 79.18
16Finland89.60 82.48
17France87.90 83.13
18Georgia62.10 74.24
19Germany86.40 81.88
20Greece87.00 82.8
21Hungary79.60 77.31
22Iceland93.60 83.52
23India44.80 70.42
24Iran71.10 77.33
25Ireland88.40 82.81
26Israel85.50 83.49
27Italy88.70 84.01
28Japan89.00 85.03
29Kenya48.70 67.47
30Latvia77.70 75.73
31Lebanon80.00 79.27
32Lithuania76.60 76.41
33Mexico62.60 75.41
34Netherlands89.50 82.78
35New Zealand86.20 82.8
36Norway90.50 82.94
37Pakistan43.10 67.79
38Panama64.40 79.1
39Poland79.60 79.27
40Qatar85.20 80.73
41Romania74.40 76.5
42Russia71.70 72.99
43Serbia75.40 76.47
44Slovakia78.60 78
45Slovenia87.40 81.85
46South Africa52.00 64.88
47South Korea85.80 83.5
48Spain89.60 83.99
49Sri Lanka72.80 77.56
50Sweden90.50 83.33
51Switzerland91.80 84.25
52Ukraine72.70 72.5
53United Arab Emirates72.20 78.46
54United Kingdom84.60 81.77
55United States81.30 79.11
56Vietnam66.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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
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.
 
Upvote 0
From the alignment it looks as though there is something after the number, what does this return
Excel Formula:
=CODE(RIGHT(E2))
 
Upvote 0
=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
 
Upvote 0
From the alignment it looks as though there is something after the number, what does this return
Excel Formula:
=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
 
Upvote 0
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
 
Upvote 0
Alternatively you could use
VBA Code:
Sub Clean160()
   Range("E:E").Replace Chr(160), "", xlPart, , , , False, False
End Sub
 
Upvote 0
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".
 
Upvote 0

Forum statistics

Threads
1,223,952
Messages
6,175,588
Members
452,653
Latest member
craigje92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top