How to Reverse or Flip Numbers in Excel

Subscribe to Channel: http://goo.gl/wN3c3p
View the Blog: http://myexcelcharts.blogspot.com

This video shows how to reverse or flip a number in a cell. For example convert the number "123" to "321". The example is done using transpose, sorting and copying it into notepad. There are other examples on the web using VBA and long formulas, but this ways tries to make it a bit more simple to do.


P.S. Feel free to provide a comment or share it with a friend!


-------------------------------------------------------------------------------------------------
WHERE TO GET THE TOOLS I USE (affiliate link):
Camtasia: http://goo.gl/qdDBnP
Microsoft products: http://goo.gl/KzkxCo

---

Closed Caption:

hi this video I'll show you how to
reverse numbers in the cell or flip
numbers around so what I mean by that
let's say for example we have these
numbers share in one cell and we want to
flip them so maybe 123 becomes 3 2 1 456
become 654 789 becomes 9 8 7 i'm a good
example of how long the number here to
show we can also do it with much longer
numbers i wanted to show a way that was
less formula intensive maybe a couple
more steps but maybe hopefully a little
bit easier to follow so the basic gist
of the steps is what we're going to do
is we're going to separate these numbers
out into different columns so each
number each integer each digit going to
have its own cell and after that we're
going to sort them and then after that
we're going to save them as a text file
and bring them back into excel as the
reverse or flipped mirror image of the
original so what I want to do first is
based on my list
I've got a list here three numbers but
maybe there's could be a list of 20 30 i
want to find out what not my maximum
length of this list is so i'm gonna go
ahead and use the length of command so
I'm gonna just put in length as a header
here to give me a nice little field here
and I want to find out the length along
this length of these items so that's
going to be using the Len command or the
Len function so and function i'm going
to be using then the text Hellion text
press ENTER and that's to tell me it has
three digits so basically if you look at
the function argument it just returns
the number of characters in the string
so that's three there so if i go ahead
and double click the fill handle it's
going to copy the formula down and it
tells me that the number of integers or
in here is nine so that's that's going
to be the most that's gonna give me an
idea of how many columns out it should
be about nine columns probably be my max
so i'm going to do is I'm going to go
ahead and put some headers out here
that's going to count up to nine so you
can go ahead and just type one and then
two and then
three and then it's got how to select it
and go ahead and just drag it out to it
hits nine here that's one way of doing
it another way of doing is using the
columns function then go and delete this
is going to just clear that helps let me
go ahead and clear this clear all and
one went the other way to do it is to
use the columns function so that's equal
columns with the s and then the array is
basically i'm going to type i'm in cell
C 1002&TYPE COC 10 and then colon c10
and they want to do is I want to go
ahead and make this a absolute cell
types absolute cell reference but the
dollar signs in front of the sea and
then the 10 by typing present the f4 key
and what happens is this column's
function what it does is it counts the
amount of columns are going in this
range from cell from c10 to see 10 is
one column gonna go ahead and show you
an example what happens when I copied /
kappa / addy you can see the format
change from cell c 10 2d 10 because the
formula over here and self c10 didn't
have the dollar signs in the second
instance of c10 you don't see the dollar
signs in the in here when I copied over
it incremented so this one from c10 2d
10 because we're in cell detent and if I
did it
if I copied over to e go ahead and copy
it over and then go and click the fill
handle just drag it over here it makes
it 3 because what's what it sees is from
c10 to eat n c2 e there are three
columns 123 same year for this one from
c10 2d tenders 212
so what I can do right now that was
dragged over here and just drag it out
and it will count up to nine from c10
2k10 that's nine columns so that's the
other way to do account the next thing I
want to do now is I want to create a
formula that takes this number and
breaks them out into the single digits
so the number one will be in one cell
the number two and another and the
number three and another and you can use
the mid function for that so i just type
equals M ID and go ahead and open
parenthesis that's is this is my texts
I'm going to reference and the start
number i want to start with
first number so i'm gonna go ahead and
reference this says because when I
reference is gonna start when the first
number here which is here and the second
number was i copied over to this cell
the second number start number is gonna
be number two which is going to be the
second number number three is going to
be the third number there and and number
four would be the fourth member here but
there's no fourth number but what I want
to do here is I also want to make one of
these references i'm going to turn it
into a mixed cell reference i forget for
this one
this has to be a have a dollar sign in
front of the a cause when I copy it down
i want to cabot stick or want to copy
that cross I want to have that column
stick so I don't want to have the dollar
sign in front i can press f4 to toggle
around present a couple of times
make sure the dollar sign is for in
front of the a I can also put a dollar
sign in front of the day by just typing
shift for now put a dollar sign in front
of da now for c10 when i'm copying this
formula across I want to go ahead and
stick with this role so I want to put
the dollar sign in front of a 10 here so
i'll go ahead and press shift for that's
the other way to doing it put a dollar
sign in front of their the number of
characters that I want to pull out what
the mid function does is it looks at the
text it starts at the number that you
want to start at which will be number
one because referencing here and how
many number of characters do we want to
pull I just want to pull one character
instead number one close the parenthesis
press enter and now it's pulled out one
if I copied this across we go and drag
the fill handle across that you can see
now is put the other digits in its
separate columns or separate cells so if
i go over here you see that the
reference is still a 11
it's this particular cell and let's look
at d10 now so it's looking at d 10 which
is too so I want to pull the second
digit out and only want to put one digit
so it's to start from the second digit i
will only pull one that's going to
number two the same thing happens here
I'm help still referencing here and sell
a $YEAR 11 and i want to go to eat n and
which is the third so it was the count
from one to three
I want to just pull one did
which is that number one there so I can
do now is that can go ahead and bring
this full hand down here it's going to
copy it down it's going to pull out for
here which is the first digit is going
to put one here which is the first digit
here we can do right now I can just drag
this field hand all the way across there
because it will still copy the same form
the over here but now it's gonna copy it
across these blanks here so i'll go
ahead and pull it out and going to do a
copy of copy that formula out and so you
see 123 is here 456 are in their own
separate cells and this nine-digit
string or number is spread out onto its
own cells right
oops i just need to spread it out one
more weight actually that's okay because
this is left justified here just to make
this little bit easier to view i'm going
to go ahead and copy the formatting this
table and we have a blue background here
i'm going to click the format painter
and this just dragged across here so
it's all blue not right now go ahead and
click that sell copy the format the
format painter will copy the format here
and just drag it amongst these cells
here so I've not got a nice little grid
here so once i have my grid laid out
here i want to take from this counter in
129 ctrl-c to copy and go into another
sheet i want to do is I want to go ahead
and pay special and the reason i want to
use pay special there's two things I
want to do with it first thing I want to
just copy the values i don't want to
copy those formulas i just want to copy
the values and then there's someone who
doesn't want to transpose it so I want
us I want to swap it so instead of
having 123456789 going across I want to
have a go down and the other values
would be copied correspondingly so once
i have values and transpose i'm going to
click OK and now you'll notice that its
transpose them so that in this instance
it was going across here like that but
now it's going down
so once this is a this is done that way
i want to resource this so instead of
having go 129 i would have to go 9 9 8 7
6 5 4 3 2 1 so this is all selected now
I'm go ahead and go under sort and
filter go to custom sort and then sort
by column may want to go from largest to
smallest so i will click OK there and
now I've got mine
987654321 and I've got this now now i
need to do is just copy the numbers that
i have i want to do now what I want to
do now is I want to try to treat three
transpose it again so i'm going to
select these range of cells the numbers
i want to swap around copy go to shoot
three and then just go ahead and
transpose so i'm going to paste and this
is the transpose command icon you can
see when you hover over it tells you
transpose and basically right now I've
got my numbers that transpose so 123
what is 321 now 45 6654 but thing is it
doesn't reside in one cell so there's a
couple more things I want to do i'm
gonna go ahead and select these range of
cells copy it and then i'll bring up a
text editor I'm just gonna go ahead and
bring up notepad once i have notepad
available i'm going to just pasted my
copied range here so i'm going to once i
pasted that i'm going to just save it
now and we just save it as a test
save it on the desktop just call it test
and then sit make sure I save it as a
text document going to click Save and
close that now and I just need to do is
open up that text document just press
ctrl 02 open and then go ahead and find
the document so here's my test document
and make sure that you select the
appropriate a text file and we can go
ahead and click on that and click open
and now it's going to ask us how we want
to open it so we can just go ahead and
select limited click Next and we don't
need any Dementors because basically
it's all going to be in one cell or in
one column so i'm going to click Next
and you can see this kind of preview for
you and it's not separated out it's all
going to be one column when i click
finish you'll see that now it's all one
it's all in one cell so we have our list
of numbers here on oneself that not have
been reversed or flipped now one thing
to also notice is if we're copying this
bunch of numbers into a text file or
something else
this probably would be fine but if we're
going to use these numbers and we want
exhale to see much as numbers we need to
do one more thing the way that XO cease
numbers versus text is
text exhale left aligns it with numbers
excel right aligned so right now its
left the line so it's telling us that
this is text and also what you might
notice is if you click into the form the
bar we have all these extra spaces there
so that's probably another reason what
reason why it thinks it's text so we
want to clean that up so to clean that
out we can use the function called Queen
and then go and tab that to ya open up
that parentheses and just select that
and press enter and now if you're going
to sell let me go ahead and actually go
ahead and just copy it down and then i'm
going to go ahead and copy this and then
paste as values i don't want that form
of there anymore once I paces values
that go ahead and select this command
here is going to that formula disappears
and now it's a value and also look no
spaces have disappeared and if i click
on each one of them i click on each one
of them that it will write a line now
there's a couple ways that we can make
these values in the cell numbers instead
of text and i'll show you one way we can
just go ahead and copy the cell go back
to the home
copy and then select this column and go
under paste and going to paste special
and just add which is add so basically
we're adding zero and once you perform a
mathematical function on a cell that has
numbers that are being seen as text is
going to turn into numbers or or it's
going to write align them in exhale will
start to see them as numbers instead of
text once i click ok you should notice
that now the numbers have right align to
the cell i double-click it to autofit
you'll see these numbers they are now
numbers they're not be no longer text
not seen this text because these are
left the line here these vendors these
are texts but these are right-aligned as
these are numbers so that's the way that
we can flip reverse numbers around in
the list here without using vba or
without using a long formula you might
see those kind of forms over on the web
but i just want to kind of show you a
way that we can do it a little bit
without formless maybe there's a couple
more steps involved but at least you
don't have to write out a long formula
for it so I hope that helps thanks for
watching