A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Sort by last two characters, not the first.



 
 
Thread Tools Display Modes
  #1  
Old July 6th 09, 05:37 PM posted to microsoft.public.excel.misc
gino986
external usenet poster
 
Posts: 1
Default Sort by last two characters, not the first.

I have a simple database; multiple rows and columns. I need to sort all the
data by info in one of the columns. I need to sort by the LAST TWO characters
of records in that column. Example of records:

R-BOILER-DA
R-FANCOILUNIT-QT
U-TRANSPAD-AN

All the data in the rows needs to stay together.
Ads
  #2  
Old July 6th 09, 05:42 PM posted to microsoft.public.excel.misc
NBVC[_75_]
external usenet poster
 
Posts: 1
Default Sort by last two characters, not the first.


gino986;405318 Wrote:
> I have a simple database; multiple rows and columns. I need to sort all
> the
> data by info in one of the columns. I need to sort by the LAST TWO
> characters
> of records in that column. Example of records:
>
> R-BOILER-DA
> R-FANCOILUNIT-QT
> U-TRANSPAD-AN
>
> All the data in the rows needs to stay together.


Create a helper column that extract those last 2 letters with formula
=Right(A2,2) where A2 has first item to sort..

Then sort the data based on this new column.


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=113003

  #3  
Old July 6th 09, 05:43 PM posted to microsoft.public.excel.misc
Jacob Skaria
external usenet poster
 
Posts: 8,521
Default Sort by last two characters, not the first.

1. Select the column. From menu Data text to columns. with delmiter as
hyphen. will split this to three columns..
2. Now sort by the 3rd column
3. Combine the 3 columns using
=A1&"-"&B1&"-"&C1
4. Now copy> paste special> values to remove the formulas...

If this post helps click Yes
---------------
Jacob Skaria


"gino986" wrote:

> I have a simple database; multiple rows and columns. I need to sort all the
> data by info in one of the columns. I need to sort by the LAST TWO characters
> of records in that column. Example of records:
>
> R-BOILER-DA
> R-FANCOILUNIT-QT
> U-TRANSPAD-AN
>
> All the data in the rows needs to stay together.

  #4  
Old July 6th 09, 05:43 PM posted to microsoft.public.excel.misc
FSt1
external usenet poster
 
Posts: 3,942
Default Sort by last two characters, not the first.

hi
use a helper column along side of your data.
assuming the example data in in column A, in the helper column, enter this
=right(A2,2)
this will draw the last 2 characters of the data into the helper column
use the helper column as the primary sort.

regards
FSt1

"gino986" wrote:

> I have a simple database; multiple rows and columns. I need to sort all the
> data by info in one of the columns. I need to sort by the LAST TWO characters
> of records in that column. Example of records:
>
> R-BOILER-DA
> R-FANCOILUNIT-QT
> U-TRANSPAD-AN
>
> All the data in the rows needs to stay together.

  #5  
Old July 6th 09, 05:50 PM posted to microsoft.public.excel.misc
Rick Rothstein
external usenet poster
 
Posts: 5,934
Default Sort by last two characters, not the first.

Assuming your data starts on Row 2, put this formula...

=RIGHT(A2,2)

in an unused column, copy it down for the length of your data, then select
all the rows of data and sort it using the above column as the primary sort
column. You can then delete the above column's formulas when you are
through. If you think you might need to sort the data again some time, use
this formula instead...

=IF(A2="","",RIGHT(A2,2))

copy it down as far as you think you might ever have data in the future and
then hide the column (rather than delete its values) after the sort (so that
you can use it again in the future).

--
Rick (MVP - Excel)


"gino986" > wrote in message
...
>I have a simple database; multiple rows and columns. I need to sort all the
> data by info in one of the columns. I need to sort by the LAST TWO
> characters
> of records in that column. Example of records:
>
> R-BOILER-DA
> R-FANCOILUNIT-QT
> U-TRANSPAD-AN
>
> All the data in the rows needs to stay together.


  #6  
Old July 7th 09, 04:36 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,912
Default Sort by last two characters, not the first.

I know this post is a couple days late but thought I would post another
method without using formulas.

Copy the entire column so's you have two identicals.

Data>Text to Columns>Delimited by>Other>Hyphen>Next.

Select columns 1 and 2 and "Do not import(skip)"

Finish. Sort on that coumn.


Gord Dibben MS Excel MVP

On Mon, 6 Jul 2009 09:37:02 -0700, gino986
> wrote:

>I have a simple database; multiple rows and columns. I need to sort all the
>data by info in one of the columns. I need to sort by the LAST TWO characters
>of records in that column. Example of records:
>
>R-BOILER-DA
>R-FANCOILUNIT-QT
>U-TRANSPAD-AN
>
>All the data in the rows needs to stay together.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't enter characters, is some sort of auto-correct on? TomCon via OfficeKB.com Excel Discussion (Misc queries) 1 April 21st 09 06:54 AM
Separating characters from a cell so I can sort on them Princess Caroline Excel Worksheet Functions 6 June 9th 08 08:36 PM
In Excel find characters when multiple characters exist w/i a cel teacher-deburg Excel Worksheet Functions 1 December 5th 05 10:22 PM
How do I sort in Excel by the no. of characters in a cell? yasmingeo Excel Worksheet Functions 3 October 21st 05 12:29 PM
Sort order for characters & symbols - does anything follow z? Neil Goldwasser Excel Discussion (Misc queries) 4 August 5th 05 05:02 AM


All times are GMT +1. The time now is 09:14 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.