+ Reply to Thread
Results 1 to 2 of 2

Sorting Book Volumes In Excel

  1. #1
    Registered User
    Join Date
    05-19-2019
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1

    Sorting Book Volumes In Excel

    Hello,

    I've had a problem with sorting a large list of books in my collection. In my first column, I have the book title and volume # in a single cell. For Example:

    Blue Exorcist Vol. 1
    Blue Exorcist Vol. 10
    Blue Exorcist Vol. 11
    Blue Exorcist Vol. 12
    Blue Exorcist Vol. 13
    Blue Exorcist Vol. 14
    Blue Exorcist Vol. 15
    Blue Exorcist Vol. 16
    Blue Exorcist Vol. 17
    Blue Exorcist Vol. 18
    Blue Exorcist Vol. 19
    Blue Exorcist Vol. 2
    Blue Exorcist Vol. 20
    Blue Exorcist Vol. 21
    Blue Exorcist Vol. 3
    Blue Exorcist Vol. 4
    Blue Exorcist Vol. 5
    Blue Exorcist Vol. 6
    Blue Exorcist Vol. 7
    Blue Exorcist Vol. 8
    Blue Exorcist Vol. 9

    The obvious problem is when sorting a mix of text and numbers, it doesn't sort the volume numbers correctly after sorting the text correctly. Is it possible to do this correctly without changing the text and volume numbers being in the same cell?

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Sorting Book Volumes In Excel

    Use the following steps. Assume your data is in Column A.
    1. Highlight the cells in Column A.
    2. Click on Data Tab-->Text to Columns
    3. Select Delimiter as Period (.)
    4. Finish
    5. Highlight Columns A&B
    6. Click on Data Tab Sort, Sort by Column B
    7. In Column C1 type formula =Concat(A1:B1) and copy down
    8. Highlight column C. Select Copy
    9. Place your cursor in A1. Paste Special
    10. Highlight columns B & C and delete.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ 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. Macro to copy Specific cells data coped from one excel book to Another excel book
    By sathyaanil37 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-14-2017, 05:38 PM
  2. Insert images in excel book from another excel book
    By Axel Aguilar in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-11-2016, 08:19 AM
  3. Macro for importing data from one excel book to another excel book...
    By jaysakle in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-24-2013, 03:05 AM
  4. Replies: 3
    Last Post: 12-27-2012, 12:26 PM
  5. Replies: 1
    Last Post: 05-17-2012, 06:13 PM
  6. Replies: 3
    Last Post: 05-17-2012, 04:54 PM
  7. Replies: 4
    Last Post: 07-18-2008, 06:48 PM

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