Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. DataText to ColumnsDelimited byOtherHyphenNext. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't enter characters, is some sort of auto-correct on? | Excel Discussion (Misc queries) | |||
Separating characters from a cell so I can sort on them | Excel Worksheet Functions | |||
In Excel find characters when multiple characters exist w/i a cel | Excel Worksheet Functions | |||
How do I sort in Excel by the no. of characters in a cell? | Excel Worksheet Functions | |||
Sort order for characters & symbols - does anything follow z? | Excel Discussion (Misc queries) |