Convert text to number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
Back
Top