Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Hatsukaichi, Hiroshima, Japan
    Posts
    904
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting selected text on 2nd character? (Excel 1997)

    Hello, imagine I have four cells in a column containing the following text:

    bite
    cake
    home
    send

    I'd like to be able to select these cells and then sort (using vba, I guess) on the second character rather than the first. That would give me:

    cake
    send
    bite
    home

    Is it possible to sort and ignore the first character?

    Thanks,

    Chris (Hunt)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting selected text on 2nd character? (Excel 1997)

    Let's say that your data are in A1:A4.
    Enter the following formula in B1:

    =MID(A1,2,100)

    The value 100 is an arbitrary value larger than the length of the longest text you expect to encounter.
    Fill down from B1 to B4.
    Click in B1 and sort ascending. (Don't select column B!)
    You can now hide column B. If you never need to sort this way again, you can even delete it.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Hatsukaichi, Hiroshima, Japan
    Posts
    904
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting selected text on 2nd character? (Excel

    Thanks Hans, I tried what you suggested with a new file and got the error message:

    "The operation requires the merged cells to be identically sized"

    Not sure why that was. I managed to get the cell widths equal and was able to create a sort as you described. However, this seemed to sort all columns. Is it possible to just sort specific selected text in one column, ignoring other text in the same column and in other columns? Sorry, my question is very basic, I'm not very familiar with Excel.

    Best wishes,

    Chris

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting selected text on 2nd character? (Excel

    You'll always have problems if you try to sort a range that contains merged cells. In general, it's best to avoid merging cells - you can use Center across Selection if you want to display a title across several cells.

    If you select only the cells with the text values and the cells with the formulas (A1:B4 in the example) and sort, you'll only sort these cells, not other cells.

  5. #5
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Hatsukaichi, Hiroshima, Japan
    Posts
    904
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting selected text on 2nd character? (Excel

    Thanks, Hans, the merge cell was created when I typed in the formula you gave. I think it was something to do with the length of 100?

    Best wishes,

    Chris

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting selected text on 2nd character? (Excel

    Excel doesn't merge cells spontaneously, so I don't think that entering a formula caused it.

  7. #7
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Hatsukaichi, Hiroshima, Japan
    Posts
    904
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting selected text on 2nd character? (Excel

    Hi Hans,

    I just tried entering the formula again and it caused the cell to expand in width. Could it be because I copied and pasted directly from Firefox?

    Best wishes,

    Chris

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting selected text on 2nd character? (Excel

    Try pressing F2 before pasting the formula.

Posting Permissions

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