Options

Excel Sort Text Shenanigans

EncEnc A Fool with CompassionPronouns: He, Him, HisRegistered User regular
This is a question I'm asking for Mrs. Enc. She has a column in excel with numbers that look like this:

1101
1283
6739
7288
1101C
2374T
4563D

What she wants is for these to sort by number and then letter, so 1101 is followed by 1101C, rather than appearing after all the 4 digit numbers are sorted out, but general sorting and turning everything to text formatting isn't doing it. The list she is working with is many thousands long and she has to manually sort these multiple times a week since COVID as her previous software solution cant be used at home.

Is there a way to get EXCEL to do this as she wishes?

Posts

  • Options
    CauldCauld Registered User regular
    edited April 2020
    If i format them all as text it works for me. To quickly change to/from text formatting I use text to columns, and go through the wizard, but obviously don't add any columns. In the last step just choose text formatting. Excel is pretty weird about getting things into and out of text formatting, so I've found that's the best method.

    Cauld on
  • Options
    Inquisitor77Inquisitor77 2 x Penny Arcade Fight Club Champion A fixed point in space and timeRegistered User regular
    If there's a logical pattern to them (e.g., they're always at the end) you can extract out the letters and then sort by number, then concatenate the letters back in. Otherwise you can't do what she wants in Excel without a custom sort/filter of some kind because numerical sorting is distinct from alphabetical sorting.

  • Options
    OrogogusOrogogus San DiegoRegistered User regular
    Cauld wrote: »
    If i format them all as text it works for me. To quickly change to/from text formatting I use text to columns, and go through the wizard, but obviously don't add any columns. In the last step just choose text formatting. Excel is pretty weird about getting things into and out of text formatting, so I've found that's the best method.

    I haven't done it that way before, but it seems to work well.

    The way I've done it is to set up a new column with =TEXT(A1, "#"), copied downwards (adjusting for the correct cell reference), then Copy -> Paste Special (Values) into a third column, and then sorting the result, selecting "Sort numbers and numbers stored as text separately" when prompted. But I think text to columns gets there in fewer steps.

  • Options
    ArtereisArtereis Registered User regular
    Cauld wrote: »
    If i format them all as text it works for me. To quickly change to/from text formatting I use text to columns, and go through the wizard, but obviously don't add any columns. In the last step just choose text formatting. Excel is pretty weird about getting things into and out of text formatting, so I've found that's the best method.

    This is also good for other quirks like dates that don't filter like dates should.

  • Options
    EncEnc A Fool with Compassion Pronouns: He, Him, HisRegistered User regular
    Cauld wrote: »
    If i format them all as text it works for me. To quickly change to/from text formatting I use text to columns, and go through the wizard, but obviously don't add any columns. In the last step just choose text formatting. Excel is pretty weird about getting things into and out of text formatting, so I've found that's the best method.

    Can you expand on this part?

  • Options
    EncEnc A Fool with Compassion Pronouns: He, Him, HisRegistered User regular
    Orogogus wrote: »
    Cauld wrote: »
    If i format them all as text it works for me. To quickly change to/from text formatting I use text to columns, and go through the wizard, but obviously don't add any columns. In the last step just choose text formatting. Excel is pretty weird about getting things into and out of text formatting, so I've found that's the best method.

    I haven't done it that way before, but it seems to work well.

    The way I've done it is to set up a new column with =TEXT(A1, "#"), copied downwards (adjusting for the correct cell reference), then Copy -> Paste Special (Values) into a third column, and then sorting the result, selecting "Sort numbers and numbers stored as text separately" when prompted. But I think text to columns gets there in fewer steps.

    This worked yesterday! Thanks! :)

  • Options
    CauldCauld Registered User regular
    Enc wrote: »
    Cauld wrote: »
    If i format them all as text it works for me. To quickly change to/from text formatting I use text to columns, and go through the wizard, but obviously don't add any columns. In the last step just choose text formatting. Excel is pretty weird about getting things into and out of text formatting, so I've found that's the best method.

    Can you expand on this part?

    Seems like you've solved it, so not sure if this is needed but:
    1. Highlight the column with the data then on the data tab click "text to columns".
    2. Click next.
    3. Uncheck 'tab' and click next. (you can probably just click next in your case).
    4. Switch the column data format to 'text' and click finish.

  • Options
    EncEnc A Fool with Compassion Pronouns: He, Him, HisRegistered User regular
    Thanks, @Cauld that works a bit quicker as well with the volume of data. Mrs. Enc likes this a bit better,

    Thanks all! This helped her a lot and saved her about 6 hours of time on a weekly basis right now.

    @ceres this can be closed. :)

This discussion has been closed.