Excel Sort Text Shenanigans

Enc

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?

Cauld

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.

Inquisitor77

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.

Orogogus

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.

Artereis

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.

Enc

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?

Enc

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!

Cauld

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.

Enc

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.