Convert text to number

S

scubadiver

I have copied some information from a website into Excel and a column of
numbers have been formatted as text (though not another column oddly enough)

I have tried using the recommendations I have found on this board but it
still won't work.

thanks
 
P

Pete_UK

Often when you copy data from a website it includes a non-breaking
space character (160), and one way of getting rid of this is:

=(LEFT(A1,LEN(A1)-1))*1

if your number is in A1 and the space character is at the end of the
number. If you have 2 characters at the end then you will need to
change the -1 to -2 in the formula. If the space is at the beginning
of the number then you will have to use this formula:

=(RIGHT(A1,LEN(A1)-1))*1

Copy the formula down for as many entries as you have, then fix the
values - highlight the cells with the formulae in, click <copy>, then
Edit | Paste Special | Values (check) | OK then <Enter>. You can now
delete the original column.

Hope this helps.

Pete
 
M

MartinW

Another way to get rid of the non breaking space is
to use Find and Replace

Select your data
Go to Edit>Replace
Find what: Alt+0160
Replace with: (leave this blank)
Click Replace All

(To use unicode hold down the Alt key type 0160 on the
number pad only, then release the Alt key.)

HTH
Martin
 
Top