Sort mixed data without delimiters

[AndiB]

I have a column of mixed data with no delimiters that I need to sort.
want the data to sort so that it is in this order: 1A, 2A, 3A....10A
11A...etc.

I know Excel by default will not sort this correctly in text or numbe
format because it looks at the number of characters---I end up wit
10A, 11A, 12A.....1A, 2A etc.

I am wondering if there is any way to sort it using a formul
(preferably) or VBA (I am a beginner with VBA).

I have tried using a concatenate formula to add a zero to the front o
1A through 9A, then copying this as a value back to the column , the
sorting and it works correctly. 01A, 02A, 03A....10A, 11A...etc.
However, 1) that is cumbersome, and 2)I don't really want the 0 at th
front of the data. Any suggestions? I am sure I must be missin
something obvious.

Thank you

 

[Guest]

Hi Andi
Do all of the numbers end in A? You could use a custom number format of #"A". Then you would just enter 1,2, 3, .. in the cells and the A would show up as part of the formatting. This way Excel will be sorting numbers instead of text and you will get the sequence you want

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- AndiB > wrote: ----

I have a column of mixed data with no delimiters that I need to sort.
want the data to sort so that it is in this order: 1A, 2A, 3A....10A
11A...etc

I know Excel by default will not sort this correctly in text or numbe
format because it looks at the number of characters---I end up wit
10A, 11A, 12A.....1A, 2A etc

I am wondering if there is any way to sort it using a formul
(preferably) or VBA (I am a beginner with VBA).

I have tried using a concatenate formula to add a zero to the front o
1A through 9A, then copying this as a value back to the column , the
sorting and it works correctly. 01A, 02A, 03A....10A, 11A...etc
However, 1) that is cumbersome, and 2)I don't really want the 0 at th
front of the data. Any suggestions? I am sure I must be missin
something obvious

Thank you

 

[Dave O]

In your example, Excel sees the combination of numbers and letters,
and treats them all as letters during the sort process. That's why
10a, 11a, 12a appear in the sort before 1a, 2a, 3a.

The only workaround I can think of for this is to write some code that
will separate the numbers from the letters, and then write those
values to newly inserted columns in your sprdsht. So if column C
contains
1a
2a
10b
11b
....then column A would contain all numbers, and column B would contain
all letters:
1 a
2 a
10 b
11 b

You could then sort first on column A, then on column B to get the
desired order. The VBA code for that is not tricky, but the results
will depend on the actual values of your sprdsht. If it's only 1a 12c
etc, then this idea will work; if it's wildly different then it may
not.

Since this post is a couple days old, please contact me via email if
you're still looking for a solution: (e-mail address removed)

Dave O

 

[AlfD]

Hi!

You clearly have a way which works for you. If your data is the sam
size and shape every time, how about recording your keystrokes as
macro?

Al

 

[AndiB]

Thank you Alf, Dave, and Mark, for your ideas! I have not had a chanc
to check back in some time----I will try out these suggestions and pos
what worked best. Thanks again for your time in responding