Excel Magic Trick 962: Convert Numbers w Comma to Number w Decimal: Formula or Text To Columns

Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm
Convert Text Numbers w Comma to Number w Decimal:
1. Formula with SUBSTITUTE Function and then Paste Special Values
2. Text To Columns

---

Closed Caption:

welcome to exhale nice trick number 962
if you want to download the sort by
969-6374 the link below the video and
this video here we have some numbers
that are imported and there are commas
where they're supposed to be decimals so
we need to convert these in essence
these are considered tax because it sees
that , and it goes up
this is text but we need to convert them
to numbers there's two ways we can do it
we do all the formula or text to columns
now if you need for some reason the
source data when updated have the result
updated that's why you do formulas but
if you just want to convert them text to
column absolutely rules data text to
columns right here
the keyboard shortcut is alt ae2 alt-a e
i'm going to say fixed with because we
don't need to separate out really were
using the text to columns not to
separate the data but to convert it so
next we don't need to do anything here
next
nothing here except for this advanced we
simply need to tell it
decimal separator is a , and then click
ok now we could click finish
if we wanted to dump it in replace and
many times you know you do not want that
data so then just click finish
however we can change the destination
cell also and so instead of doing it
right there if you do it there it just
replaces the data which is great for
many cases but in this case I'm going to
keep the original data and then jump in
here and then click finish and boom
there it is for a formula will use the
substitute again why would you use
why do you use formulas why were
formulas invented by brooklyn and
frankston because source data you put in
cells then formulas automatically update
so here this is going to be a little bit
more involved than doing this but if
some reason this changes you want to
update this is the method all right so
check that out
old text well I want to in double quotes
fine too ,
in double quotes , the new text a double
quote the decimal double quote and then
closed parenthesis
now a couple of things here substitute
will deliver a text item and you can
immediately tell if the default number
general number format is here you can
immediately tell that this is not a
number because it's aligned to the left
well there's a simple trick informed us
to convert it
plus any are any math operation on a
number stored as text will convert it
back to a number so it doesn't matter
you can x 1 / 1 exponent i'm just going
to add zero control enter double click
and send it down
now that's dynamic right so if i change
this to 65 moment that changes
so that's why do formula if you happen
to do this and you wanted them off to
the side or you didn't remember how to
do text to columns and you didn't want
the form and then you have to copy paste
special values there is a great way to
quickly copy some numbers over to a
different column keep the formula but
have numbers dumped here you highlight
and point to the edge and when you see
you move cursor instead of left clicking
right click drag it over now see it's
dragging it's moving it's got a drag
when you let go
cool pop-up menus and you simply click
on copy heroes values only and boom
there you have it again formulas
that's what you want a dynamic and text
to column there's the steps
see you next video