Advanced Sort/ look

applemanxxx

unread,

Jun 17, 2013, 10:34:28 AM6/17/13

to

Good morning everyone,

I am trying to sort a document based on the information in one
particular column, this Colum contains the following alpha numerical
string “F907E231220084”. I need to help in figuring out how to sort
based on the last 4 digits only. I appreciate any and all help. You
don’t have to write a formula for me just point me in the direction I
will try to figure out the rest.

--
applemanxxx

Ron Rosenfeld

unread,

Jun 17, 2013, 11:06:57 AM6/17/13

to

(Not writing formula mode): Extract the last four digits into an adjacent column, then sort on that column. When you're done, you can either delete the column or hide it.
Note that the sort will be different if your results are "text" or "real numbers", so use an appropriate formula depending on what you want.

applemanxxx

unread,

Jun 18, 2013, 12:53:46 PM6/18/13

to

'Ron Rosenfeld[_2_ Wrote:
> ;1612356']On Mon, 17 Jun 2013 15:34:28 +0100, applemanxxx
> <applemanx...@excelbanter.com> wrote:
> -

> >
> >Good morning everyone,
> >
> >I am trying to sort a document based on the information in one
> >particular column, this Colum contains the following alpha numerical

> >string “F907E231220084”. I need to help in figuring out how

> to sort
> >based on the last 4 digits only. I appreciate any and all help. You

> >don’t have to write a formula for me just point me in the
> direction I
> >will try to figure out the rest.-

>
> (Not writing formula mode): Extract the last four digits into an
> adjacent column, then sort on that column. When you're done, you can
> either delete the column or hide it.
> Note that the sort will be different if your results are "text" or "real
> numbers", so use an appropriate formula depending on what you want.

Well thank you for that, I ve tried it and it almost worked....However,
I have to manually input the formula in to each cell, here what
happens:
=MID(A3,ROW(11:11),4) – 1 CELL The original formula returns 4
digit number
=MID(A4,ROW(12:12),4) – 2 CELL The original formula returns 3
digit number
=MID(A5,ROW(13:13),4) – 3 CELL The original formula returns 2
digit number
=MID(A6,ROW(14:14),4) – 4 CELL The original formula returns 1
digit number
ETC….. I think you get the picture …ROW(N, N) keeps changing
as I try to copy it down, thus, as I stated it earlier, I must do
manually…. Any thoughts??

--
applemanxxx

Claus Busch

unread,

Jun 18, 2013, 2:01:47 PM6/18/13

to

Hi,

Am Tue, 18 Jun 2013 17:53:46 +0100 schrieb applemanxxx:

> I have to manually input the formula in to each cell, here what
> happens:
> =MID(A3,ROW(11:11),4) – 1 CELL The original formula returns 4
> digit number
> =MID(A4,ROW(12:12),4) – 2 CELL The original formula returns 3
> digit number
> =MID(A5,ROW(13:13),4) – 3 CELL The original formula returns 2
> digit number
> =MID(A6,ROW(14:14),4) – 4 CELL The original formula returns 1
> digit number
> ETC….. I think you get the picture …ROW(N, N) keeps changing
> as I try to copy it down, thus, as I stated it earlier, I must do
> manually…. Any thoughts??

try:
=RIGHT(A3;4) and copy down.
You can copy the helper column and pastespecial paste values and apply
custom numberformat 0000.

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

applemanxxx

unread,

Jun 18, 2013, 3:19:11 PM6/18/13

to

Claus Busch;1612364 Wrote:
> Hi,
>
> Am Tue, 18 Jun 2013 17:53:46 +0100 schrieb applemanxxx:

> -

> > I have to manually input the formula in to each cell, here what
> > happens:
> > =MID(A3,ROW(11:11),4) – 1 CELL The original formula returns 4
> > digit number
> > =MID(A4,ROW(12:12),4) – 2 CELL The original formula returns 3
> > digit number
> > =MID(A5,ROW(13:13),4) – 3 CELL The original formula returns 2
> > digit number
> > =MID(A6,ROW(14:14),4) – 4 CELL The original formula returns 1
> > digit number
> > ETC….. I think you get the picture …ROW(N, N) keeps
> changing
> > as I try to copy it down, thus, as I stated it earlier, I must do

> > manually…. Any thoughts??-

>
> try:
> =RIGHT(A3;4) and copy down.
> You can copy the helper column and pastespecial paste values and apply
> custom numberformat 0000.
>
>
> Regards
> Claus Busch
> --
> Win XP PRof SP2 / Vista Ultimate SP2
> Office 2003 SP2 /2007 Ultimate SP2

Thank you Claus, I ve found the same solution, but thank you
nevertheless .

--
applemanxxx