How to Convert Text to a Number in Excel 2007

http://www.facebook.com/savoirfairetraining
http://www.savoirfaire.net.au

Often numbers which are copied or imported from other software packages will be stored as text in Excel. This video will show you 2 simple methods of converting the text to numbers.

---

Closed Caption:

yeah
yeah
yeah
yeah
sometimes when you import data or you
copy and paste data from other software
packages numbers will be formatted or
stored as text in excel rather than
stored as a number
even when you try and change the format
of the text to a number
it still doesn't work this video will
show you a couple of different ways that
you can convert text to a number here in
column a
I have a series of product numbers you
can see in the top left hand corner of h
sell that there is a little green
triangle
when i click on one of the cells there
is a little ! or alert that is trying to
tell me something here over on the right
hand side if I hover my mouse over that
alert
then a little drop down arrow appears to
the right of it and the little comment
appears to tell me that the number in
this cell is formatted as text or
preceded by an apostrophe another
telltale sign that the item in this cell
is stores text and not as a number is
the fact that it is aligned to the left
of the cell numbers are usually aligned
to the right of a cell if i click on the
drop-down arrow to the right of that
alert symbol
you'll see that the very first item in
the list is highlighted and it is
telling me that the number is stored as
text to change that
I simply need to select the second item
in the list which is convert text to
number you'll see now that the little
green triangle has disappeared and the
number is aligned to the right hand side
of the cell whereas the ones beneath it
are aligned to the left because they are
still stored as text now rather than
converting h sell one by one
I can convert all of these cells at once
by highlighting the cells hovering my
mouse over the alert symbol clicking on
the drop-down arrow next to the alert
symbol and then by selecting convert
text to number
and it should work for all of those
cells i had highlighted
i'm going to click on the undo button on
the quick access toolbar
because i want to show you another way
you can convert text to a number
another common way of converting is
simply by taking the number that is
stored as text and multiplying it by the
number one
so for example in cell b2 here i can add
the formula equals a to x 1 and then
copy the formula down the column but you
then end up with two columns of the same
data now you can obviously then paste
this data as values and delete one of
the columns so it's not a big issue
however there is another way you can
multiply the data by the number one
without actually creating a duplicate
column
let me just too late that column i added
and in an empty cell here i'm going to
type the number one if I copy that
number one by selecting the cell and
then clicking on the copy button in the
Home tab I can then use the paste
special command to multiply all of these
cells here by the number one to do that
I select the cells and then click on the
drop-down arrow underneath the paste
button select paste special and then
click on multiply in the dialog box that
appears and finally click on OK
you'll see that the cells and now
formatted as numbers and they are
aligned to the right hand side of the
cell and because that particular method
doesn't store the multiplication as a
formula i can simply delete the number
one in cell c2 here and the numbers in
column a still remain formatted as a
number i hope you found this tutorial
useful
yeah