sort column by last digit

Lets say I have a column like:

12435G
19995
188884G
188465
277745
etc etc etc

...and I want to sort it so that the ones that end with the letter G
are isolated. Is there a way to do this?


-- 
Kepf
------------------------------------------------------------------------
Kepf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30611
View this thread: http://www.excelforum.com/showthread.php?threadid=502969

0
1/19/2006 5:18:59 PM
excel 39879 articles. 2 followers. Follow

2 Replies
411 Views

Similar Articles

[PageSpeed] 16

In a helper column, add

=IF(RIGHT(A1)="G","G","")

then sort by the helper column.

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Kepf" <Kepf.21w6jc_1137691208.9198@excelforum-nospam.com> wrote in message
news:Kepf.21w6jc_1137691208.9198@excelforum-nospam.com...
>
> Lets say I have a column like:
>
> 12435G
> 19995
> 188884G
> 188465
> 277745
> etc etc etc
>
> ..and I want to sort it so that the ones that end with the letter G
> are isolated. Is there a way to do this?
>
>
> -- 
> Kepf
> ------------------------------------------------------------------------
> Kepf's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=30611
> View this thread: http://www.excelforum.com/showthread.php?threadid=502969
>


0
bob.phillips1 (6510)
1/19/2006 5:56:25 PM
If you also want the numeric part sorted, you will need another helper 
column.

Assuming Bob's helper column is B
=IF(B1="G",VALUE(LEFT(A1,LEN(A1)-1)),A1)
then sort the data by column B then C
If you want the Gs at the top, sort column B descending.
-- 
Ian
--
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message 
news:ONEdPHSHGHA.2064@TK2MSFTNGP09.phx.gbl...
> In a helper column, add
>
> =IF(RIGHT(A1)="G","G","")
>
> then sort by the helper column.
>
> -- 
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Kepf" <Kepf.21w6jc_1137691208.9198@excelforum-nospam.com> wrote in 
> message
> news:Kepf.21w6jc_1137691208.9198@excelforum-nospam.com...
>>
>> Lets say I have a column like:
>>
>> 12435G
>> 19995
>> 188884G
>> 188465
>> 277745
>> etc etc etc
>>
>> ..and I want to sort it so that the ones that end with the letter G
>> are isolated. Is there a way to do this?
>>
>>
>> -- 
>> Kepf
>> ------------------------------------------------------------------------
>> Kepf's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=30611
>> View this thread: 
>> http://www.excelforum.com/showthread.php?threadid=502969
>>
>
> 


0
me1 (409)
1/19/2006 8:13:10 PM
Reply:

Similar Artilces:

Last record in detail
-- Taylor ?! Regards Jeff Boyce Microsoft Office/Access MVP "TaylorLeigh" <TaylorLeigh@discussions.microsoft.com> wrote in message news:34186B73-4DF3-4680-9F12-73A84F2D62D7@microsoft.com... > > -- > Taylor ...

find column with text data in row 1
I have the following macro which bounces me back and forth between b:k and m:u Private Sub Workbook_SheetBeforeDoubleClick( _ ByVal Sh As Object, ByVal target As Range, Cancel As Boolean) If Not Intersect(target, Range("B:J")) Is Nothing Then target.Offset(0, 11).Select If Not Intersect(target, Range("M:U")) Is Nothing Then target.Offset(0, -11).Select End Sub Now I would like to build on that. My K Column is my delimiter with the static text 'Totals'. I would like to be able to have the functionality of the above without have the number of columns static, so...

Creating a matrix from columns
I have an Excel Sheet A with 3 columns. Col A=Site; Col B=Part; Col C=Quantity. Rows of site and parts data are added daily and may contain the same site or same part number along with quantities for each row in Col C. I then create Sheet B with unique sites and unique parts so that I now have a matrix of unique sites in Col A and unique parts (transposed)in Row 1. In the data cells of the matrix, I need to know how to reference back to the Sheet A to summarize the quantities (Col C) of each unique part located at each unique site (VLOOKUP? SUMIF? Some other database function?) Any...

adding digits to front/end of fax numbers
how do i add "1" before each fax number and ",,7741" at the end of each one? we have to dial one to dial long distance and we have a code to use the long distance in our office. please help quickly. Thanks Hi! Fax number in A1: =1&A1&",,7741" If you want a space after the 1: =1&" "&a1&",,7741" Biff "Luke" <Luke@discussions.microsoft.com> wrote in message news:D1C25AF0-7FBC-43CE-855D-FCCA77CD9759@microsoft.com... > how do i add "1" before each fax number and ",,7741" at the en...

sorting #42
I have a column of names with last name first. I want to have Excel sort these so that the FIRST name is first. Can this be done? You'll need to pull the first names into a different column. While you can do that with formulas, it is usually faster & easier to insert two new columns, copy & paste the names into the left new column, and then use Data|Text to Columns on that copied data, splitting the names where there's a comma or space, depending on how the list is set up. "Patrick" wrote: > I have a column of names with last name first. I want to have...

How do I zip sort mailing list when I have 5 digit AND 9 digit zip
I have a mailing list with zips in XXXXX and XXXXX-XXXX all mixed in. When I sort them, it seems to put all the 5 digits on the top half and 9 digits on the bottom half. I have made sure the column settings are set to special/zipcode. I have also tried setting them as Zip Code + 4 but it converts all the XXXXX versions to 0000X-XXXX. What am I doing wrong? Convert to TEXT -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Greg" <Greg@discussions.microsoft.com> wrote in message news:97AB96D9-EAE6-4E6F-8AA1-1DE4137EDA06@microsoft.c...

a-z sort on sheets
can anyone tell me pls if it is possible to sort my worksheets alphabetically? I have about 30 or so which I would like to sort after renaming them. http://www.cpearson.com/excel/sortws.htm "Dale" <daleathome@blueyonder.co.uk> wrote in message news:1Wsgf.7040$Hm2.2847@fe1.news.blueyonder.co.uk... > can anyone tell me pls if it is possible to sort my worksheets > alphabetically? I have about 30 or so which I would like to sort after > renaming them. > ...

How do I modify an existing worksheet to remove columns & contents
I am working with a very large spreadsheet and want to modify it using only certain columns and data in order to keep from re-doing the entire worksheet. ANybody out there in cyberspace got suggestions or good reference sources I can use? Gil There are just too many scenarios to ponder without getting some more details on what you wish to do. Please be a little more specific in your description. Gord Dibben Excel MVP On Wed, 9 Feb 2005 18:15:06 -0800, "Gil Gray" <Gil Gray@discussions.microsoft.com> wrote: >I am working with a very large spreadsheet and want to m...

How do I link columns so data flows from 1 column to another like.
Is there a way to link columns in Excel, so that data that's entered flows from one column to the next? There's a function similar to this in Quark. Anybody know anything about this? Regards, If I understand the question correctly, and the information is being typed into the cells, you could try this: Select a block of cells, say A2:C10 Then, type a value. Press enter. If yuo press enter after each value entered, when you enter a value in A:10 and press enter, you're active cell will be B2. tj "M. Frazel" wrote: > Is there a way to link columns in Excel, so t...

Using Index across several columns
Hi, I have this table that span from colum E:J 1-4 14% 113-116 42% 225-228 71% 5-8 15% 117-120 43% 229-232 72% 9-12 16% 121-124 44% 233-236 73% 13-16 17% 125-128 45% 237-240 74% The table is complete for 365 days, I just omited the rest to save space. Cell A1 has number of days. I want to find the percentage that matches that day. I have come up with this formula: =INDEX(F1:F28,ROUNDUP(A1/4,0)) Which works fine, IF the number of days is less than 16 which is normal because index only looks in column F. I would like to expand this formula to also look...

email addressbook name order (last, first)
I'm using stand alone Outlook 2007. When I create a new email then hit "To" and my address book come up all the names are arranged first, last. How do I order them last, first? Thank you Change the sort here: Tools menu > Account Settings > Address Book tab > Highlight Outlook Address Book > Click Change "Dave R" <rezin8@threshold-studio.com> wrote in message news:D14B3FCF-D6F2-453D-8BEC-B398C687ED1F@microsoft.com... > I'm using stand alone Outlook 2007. When I create a new email then hit > "To" and my address book come up...

Comparing multiple columns
hi all, I need to compare two excel sheets(sheet1,sheet2) where in i have the employee hours worked for a fortnight. One sheet is generated using an exe file and the other is the one that has got uploaded to backend. Need to find whether the hours worked match one to one. For e.g My sheet 1 would have data like this....(without the header) 0 4 8 6 8 6 0 5 3 0 8 8 8 8 0 4 4 4 2 0 8 8 8 8 0 11 11 10 9 0 0 0 0 0 0 (55 records) Sheet 2 0 4 8 6 8 6 0 5 3 0 8 8 8 8 ...

How do you change the size of a range of cells in a column/row
without changing all cell sizes in those rows or columns You can't, width and height are properties of columns and rows, not cells. You can merge cells in two columns or rows to combine their width or height. In article <0F00CD03-E47C-44AD-9185-89BEEF4D3FEB@microsoft.com>, SOkoll <SOkoll@discussions.microsoft.com> wrote: > without changing all cell sizes in those rows or columns ...

Change Default Columns?
I use MS Outlook 2007 SP2. Is there a way to make all folders, or, at least all NEW folders have a standard set of columns, which is different than the default columns? For example I don't need the "Status" or "Size" columns. Rather than delete them after I create a new folder, I'd like the default configuration NOT to include them Thanks! On Jan 31, 4:50=A0am, Don Green <dmg...@gmail.com> wrote: > I use MS Outlook 2007 SP2. =A0 > Is there a way to make all folders, or, at least all NEW folders have > a standard set of columns, which is differ...

Can I add more columns beyond col. IV?
I realize this may be a elementary question to most, but I hope there' a fix. I'm making a spreadsheet that requires about 10 more columns (beyon column IV) than the normal new Excel file allows (to the would-b column JE). Is there any way for me to extend the sheet and add mor columns? Thanks -- Rick_ ----------------------------------------------------------------------- Rick_B's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=596 View this thread: http://www.excelforum.com/showthread.php?threadid=39343 No, you are limited to 256 columns -- Regard...

Find MAX of column from using 2 criterias
Spreadsheet (duh) with 3 columns: Month, Vehicle Licence #, Odometer readings. Now looking at the Month and Vehicle Licence #, what is the lowest odometer reading and also what is the highest odometer reading, so as to calculate the distance travelled. Do not want to use Pivot tables - as columns will grow. Do not want to use array formula as end user is as useless as putting butter on bread with a toothpick. Thanks in advance. Hi, Try this array formula. =MIN(IF(MONTH(A1:A20)=D1,IF(B1:B20=E1,C1:C20))) Change MIN to MAX to get the max value D1 is a number i.e. 4 fo...

using row number or column letters in formula
Now I'm trying to use a row number in a formula so I don't have to type the same formula individually on each row 12 times! My formula is currently this =SUM(INDIRECT("'"&'Control Sheet'!$F$57&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$58&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$59&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$60&"'!G7")+INDIRECT("'"&'Control Sheet'!$F$61&"...

Largest non-blank sequence in row or column
I'm trying to create a formula which will count the largest number of sequentially non-blank cells in a column or row. e.g. if the row had the following columns: |y|y|y|y| | |y|y|y|y|y|y| |y|y|y|y|y| the value would be 6 because it is the largest number of "y" values next to each other. Any Ideas please let me know. I'm using the formula to work out the longest number of days I do a particular task in a row. I update the spreadsheet daily and put a "y" in the row if I do that task. On Mar 11, 10:57 pm, David <david.mcdow...@gmail.com> wrote: > I'm t...

Lock columns
I have this excel spreadsheet that I need to distribute to people to use to fill in but because I need to convert this spreadsheet to a specific format once they send it back to me I don't want them to be able to change the column width. Is there a way to lock the column width but still have people be able to enter data and be able to still use the tab key to move from cell to cell? CTRL+A / Format / cells / Protection / Uncheck Locked. Then Tools / Protection / Protect Sheet - password optional. -- Regards Ken....................... Microsoft MVP - Excel ...

Copy range to different columns depending on cell value
What I would like to do is look at a range (A6:IXXX), where XXX is the last row of the range, and if there is NOT an "X" in I6, copy that row to another range (K6:S6), this would look at each row and copy to the new range if "I" is empty. Is this possible to do? Try this Sub MoveRange() Dim I As Long Dim aLastRow As Long Dim dLastRow As Long Dim ws As Worksheet Dim aRange As Range Dim dRange As Range Set ws = Worksheets(2) aLastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row For I = 6 To aLastRow Set aRange = Range(&q...

Insert symbol as a header for a column
I would like to insert 1/5 as a fraction header for a column. You can select from the symbol menus say for instance under the Airal choices menu as there are a small number of fraction signs to select. I do not want to particular type in 1/5 or 1/5th as I want the looks that are obtained from using the symbol inputs. Does anybody know how you might achieve this? Thanks -- James The code for one fifth is 8533. Insert the following UDF: Function unicd(r As Long) As String unicd = ChrW(r) End Function Format A1 as Ariel MS Unicode In A1 enter: =unicd(8533) Us...

Formatting a Column....
I want to do a continuous +1 in A1 thru A17 - ex. A1 = H03860, A2 = H03861, A3 = H03862 ------ yes I feel very stupid asking this! HI Type the first 2 numbers in A1 and in A2 then click the bottom of cell 2 and drag down has far has you like. They will increment by themselves HTH John "dawn brist" <dawnbrist@discussions.microsoft.com> wrote in message news:7C5ADF58-F78E-47FB-A8DB-F8073D11450A@microsoft.com... >I want to do a continuous +1 in A1 thru A17 - ex. A1 = H03860, A2 = H03861, > A3 = H03862 ------ yes I feel very stupid asking this! > Hi Again I should h...

Comparing stacked columns
I am trying to create the following chart. How do I do it in excel. Is there and additional template/addin that would help in this? Data: Widgets in the market(by geography) My Widgets 50 30 40 20 30 10 Chart: I am trying to create two stacked bars on the X axis, one for "Widgets in the market" and "My Widgets". I then want to depict that "My widgets" is x% of Widgets in the market. I would like to be able to draw a line from one cell to another (comparing the reduction in share across geographies). I wish I could upload a picture somewhere! Tha...

How to convert contents of column from numeric data type to text
you know how sometimes when you paste a value into a cell it reads like a number but Excel thinks its text and aligns it to the left and gives you a little dropdown menu with options such as keep this value as text or to convert it to numeric data. I have a whole column of mostly numbers (and excel is storing them as numeric data) and want to convert them to text. I don't have that menu option available since excel thinks they're numbers. Is there a way to convert numeric data to text? Thanks. -- moondaddy@noemail.noemail If you don't mind a helper column, then you c...

CF for Row when CF is applied to Column
I've read through the postings but can't find an answer to my issue. Seems simple enough but I can't figure it out. I have a spreadsheet which I've applied conditional formatting to a column so that if any text appears in a cell, to color that cell yellow. What I'd like is for the row from A:O be colored yellow, not just the J cell. I'm using 07. Could someone please explain the steps in English to me or point me to the appropriate post? Cuz, I ain't figrin it out! Thanks for the help in advance! --Dax -- I would give my left hand to ...