Sorting "X1 X10 X2" in correct order as "X1 X2 X10"

[bkm]

I'm working with columns with "addresses" which are always constructed
with one or more letters (Or a word) first and then follows a number,
like f.ex.:

X1
X10
X2

But if I sort them in excel from top to bottom/a-z they are not sorted
correctly, it always comes out with "X10" before "X2" like shown above.
I would like to sort them so that they look like this:

X1
X2
X10

But I can't seem to get it to work... Formatting the cells doesn't seem
to help either.

 

[bkm]

And I forgot to mention that I am also trying to sort by letters as
well, like f.ex.:

D1
D2
D10
E1
E2
E10
X1
X2
X10


//Bjorn

 

[Michel Bru]

1) Data/Sort menu.
2) Click on the 'Options' button and select the corresponding element
in the combo box.

Best regards.

Michel Bruyère.

1) Het menu van gegevens/van de Soort.
2) Klik op de knoop van ' Opties ' en selecteer het overeenkomstige
element in de combodoos.

Begroetingen.

Michel Bruyère.

 

[Ryan.Chowdhury]

If, at the moment, you have a fixed number of letters preceeding your
numbers, I would recreate your column using a few formulas such that
you end up with:

D001
D002
D010
E001
E002
E010
X001
X002
X010

here are the formulas assuming your raw data begins in A1

1. separate the letters and numbers
B1: =left(A1,1) <-- the last parameter assumes there's only 1 letter
C1: =right(A1, len(A1)-1) <--again the 1 assumes only 1 letter
D1: =TEXT(C1,"00#") <--this reformats the number into a three digit
format
E1: =B1&D1 <0--combines B1&D1

Now, you can sort on E1 with desired results.

 

[bj00rn]

I see, but I don't have anything else than a couple of lists in the
combo box with "January, February, March" and so on. And if I had to
make a custom list it would have to contain millions of combinations so
to speak...

Just try it; Put

"D1" in cell A1
"D10" in cell A2
"D2" in cell A3

in your excel and try to sort it. It should be easy to just sort a-z,
but it just won't work.


Regards,
Bjorn

 

[bj00rn]

I understand what you're saying, but I'm afraid there is no consistency
in the number of letters and numbers in my excel file. I'm working with
application tag-lists, and the software we are using demands perfection
in the tag-list, and even the smallest rearranging of the content of
the cells would mean that the software which depends on the addresses
would stop working

There is about 5,000 rows with 16 columns each, in the .dbf file, and I
want to sort these by one of the columns with adresses.

Here is just one row in the .dbf-file, and I want to sort it by the
column "UN800.ModBusCOM3.2-770" (Which is not consistent, the cells
could contain f.ex. "r1168[6]" or "d1454" and so on)
it could be) column.

DG2_Stop_, DIGITAL, CDPDev, UN800.ModBusCOM3.2-770, 0, 1, 0, 1, on/off, ###.##EU, Output, 0x02c0a258


I'm probably asking about a lot... I realize that this might not be
possible without some code. But I'm hoping

Thanks for trying, I appreciate it.

Regards,
Bjorn

 

[Ryan.Chowdhury]

Bjorn,

Two questions:

1. It looks like there are characters in the sort column as well, "["
and "]" ? That may alter the results.
2. Also, the examples you gave only had one letter. If you can say
with some confidence that the labels have 1,2, up to 7 characters
before the numbers, you can modify the "LEFT" formula above, to handle
different cases, using the "IF" formula and the "ISTEXT" formula. The
number of digits is not important.

Also, you would not be removing the original tags, just creating a
"helper" column that interprets the tag and allows you to sort. So the
original data would be preserved.

I'm having difficulty seeing why you would sort this column. Do you
want to group similar items? That would be a different problem. Can
you describe your problem in more detail?

Ryan

I understand what you're saying, but I'm afraid there is no consistency
in the number of letters and numbers in my excel file. I'm working with
application tag-lists, and the software we are using demands perfection
in the tag-list, and even the smallest rearranging of the content of
the cells would mean that the software which depends on the addresses
would stop working

There is about 5,000 rows with 16 columns each, in the .dbf file, and I
want to sort these by one of the columns with adresses.

Here is just one row in the .dbf-file, and I want to sort it by the
column "UN800.ModBusCOM3.2-770" (Which is not consistent, the cells
could contain f.ex. "r1168[6]" or "d1454" and so on)
it could be) column.

DG2_Stop_, DIGITAL, CDPDev, UN800.ModBusCOM3.2-770, 0, 1, 0, 1, on/off, ###.##EU, Output, 0x02c0a258


I'm probably asking about a lot... I realize that this might not be
possible without some code. But I'm hoping

Thanks for trying, I appreciate it.

Regards,
Bjorn

If, at the moment, you have a fixed number of letters preceeding your
numbers, I would recreate your column using a few formulas such that
you end up with:

D001
D002
D010
E001
E002
E010
X001
X002
X010

here are the formulas assuming your raw data begins in A1

1. separate the letters and numbers
B1: =left(A1,1) <-- the last parameter assumes there's only 1 letter
C1: =right(A1, len(A1)-1) <--again the 1 assumes only 1 letter
D1: =TEXT(C1,"00#") <--this reformats the number into a three digit
format
E1: =B1&D1 <0--combines B1&D1

Now, you can sort on E1 with desired results.

 

[Pete_UK]

What Ryan is suggesting is that you add extra columns to what you have
already (helper columns) and use these to help sort your data. Once you
have accomplished this, you can delete the helper columns so that you
are left with the original data structure, with the data unchanged, so
your software will still be able to work with the .dbf file.

One thing that might help is to obtain a unique list of values for the
column "UN800.ModBusCOM3.2-770". To do this, insert a new sheet and
copy this column (including the heading) to column A of the new sheet.
With the data and heading highlighted, click Data | Filter | Advanced
Filter and in the dialogue box click "Copy to another location"
(specify $C$1), and "Unique records only" and click OK. You might like
to sort column C so that you can easily see the variation in
letters/numbers that you have.

You could then apply some of Ryan's formulae to this column to try to
get the data into some consistent format and then feed it back to the
main sheet.

You could, in fact, make use of this reduced list to control your sort
order - put 1, 2, 3 etc down column D to indicate what order you would
like the item in column C to appear (keep sorting these two columns on
D to check for positions), and then in your main sheet column 17 you
could have a formula like :

=VLOOKUP(D2,Sheet2!C$2$2000,2,0)

to return the sort order to the main sheet. You can then sort on this
field then delete it, and Sheet 2, when you are finished.

Hope this helps.

Pete

I understand what you're saying, but I'm afraid there is no consistency
in the number of letters and numbers in my excel file. I'm working with
application tag-lists, and the software we are using demands perfection
in the tag-list, and even the smallest rearranging of the content of
the cells would mean that the software which depends on the addresses
would stop working

There is about 5,000 rows with 16 columns each, in the .dbf file, and I
want to sort these by one of the columns with adresses.

Here is just one row in the .dbf-file, and I want to sort it by the
column "UN800.ModBusCOM3.2-770" (Which is not consistent, the cells
could contain f.ex. "r1168[6]" or "d1454" and so on)
it could be) column.

DG2_Stop_, DIGITAL, CDPDev, UN800.ModBusCOM3.2-770, 0, 1, 0, 1, on/off, ###.##EU, Output, 0x02c0a258


I'm probably asking about a lot... I realize that this might not be
possible without some code. But I'm hoping

Thanks for trying, I appreciate it.

Regards,
Bjorn

If, at the moment, you have a fixed number of letters preceeding your
numbers, I would recreate your column using a few formulas such that
you end up with:

D001
D002
D010
E001
E002
E010
X001
X002
X010

here are the formulas assuming your raw data begins in A1

1. separate the letters and numbers
B1: =left(A1,1) <-- the last parameter assumes there's only 1 letter
C1: =right(A1, len(A1)-1) <--again the 1 assumes only 1 letter
D1: =TEXT(C1,"00#") <--this reformats the number into a three digit
format
E1: =B1&D1 <0--combines B1&D1

Now, you can sort on E1 with desired results.