+ Reply to Thread
Results 1 to 6 of 6

how can I sort column by last letter

  1. #1
    Registered User
    Join Date
    11-19-2017
    Location
    germany
    MS-Off Ver
    2011
    Posts
    6

    how can I sort column by last letter

    Dear Excel Experts,

    I am trying to sort an excel-spread sheet by the last letter of each word and am encountering some difficulty. Column A is filled with words which I want sorted. According to this link (google “how to alphabetize in excel last letter”, it’s the first hit on extendoffice) I have placed “=RIGHT(A1,1)” in B1 and subsequently dragged the ‘Fill Handle’ down to the last word in the A column.
    Then I click on the ’Sort’ button with the intent to select ‘Sort A to Z’. Regretfully all the options in the ‘Sort Button’ are greyed out. If I try to press ‘enter’ on my keyboard I get the following error message:

    “The formula you typed contains an error.
    • To get assistance in entering a function, click OK, then click Function on the Insert menu.
    • If you are not trying to enter a formula, avoid using an equal sign (=) or minus sign (-), or precede it with a single quotation mark (‘).”

    Any ideas what I’m doing wrong? Many thanks for your time and expertise.

    Peter

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: how can I sort column by last letter

    I suspect your Regional settings might require you to use semi-colon ( ; ) instead of comma ( , ).
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    11-19-2017
    Location
    germany
    MS-Off Ver
    2011
    Posts
    6

    Re: how can I sort column by last letter

    Thank you TMS for your swift reply!

    I have now changed the comma for a ";". The resulting B1 box has the following in it: "=RIGHT(A1;1)+A1:A28". I am now receiving the following message in the B1 box when I press 'enter': "#VALUE!"

    Also, when I move the 'fill handle' down to select all the cells in question (ie the cells in the A column) the sort button continues to be greyed out.

    (I am indeed in Germany and I do change between typing in german and english, but, my Excel menu is in english for what that's worth). Also, my excel is "for Mac 2011 Version 14.1.3. I'd prefer not to have to update it for this task.)

    Any ideas what the best next step is? (Many thanks again for your help!)
    Last edited by peterspiano; 06-21-2019 at 06:44 AM.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: how can I sort column by last letter

    In B1, I think you just need:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Press Enter and then use the Fill Handle to drag the formula down.

  5. #5
    Registered User
    Join Date
    11-19-2017
    Location
    germany
    MS-Off Ver
    2011
    Posts
    6

    Re: how can I sort column by last letter

    Aha! So now column B has all of the last letters of column A. And now I guessing the correct method is just to sort the whole thing by Column B? Is there a way to do without having to have a Column B with the extra letters? (If not it's not so bad, I can just 'white it out' and pretend it's not there or make the column very thin). In any event, thank you very much!

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: how can I sort column by last letter

    You're welcome.

    Is there a way to do without having to have a Column B with the extra letters?
    I don't think so.


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 03-29-2018, 07:58 AM
  2. [SOLVED] How to Sort the column second letter wise in alphabetically
    By Shi in forum Excel General
    Replies: 1
    Last Post: 05-18-2016, 05:31 AM
  3. Replies: 6
    Last Post: 03-30-2016, 04:28 PM
  4. Replies: 1
    Last Post: 12-20-2010, 06:30 PM
  5. Replies: 6
    Last Post: 10-13-2005, 05:05 AM
  6. [SOLVED] How to replace column letter in refferences with a function using the old column letter?
    By Dmitry Kopnichev in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-13-2005, 05:05 AM
  7. Replies: 2
    Last Post: 05-09-2005, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1