Custom Format with Numbers and Letters?

I have a column with values like these

A1
A-3 // reads: A (minus 3)
J24
J-2
A24
...

Now I want to sort them first by the leading letter. Then by the following number. But Excel reads the minus as dash and sorts it like that

Please Login or Register  to view this content.

There are special occasions where we got a trailing zero, too

Please Login or Register  to view this content.

What I want my A-Z sorting to look like

Please Login or Register  to view this content.

Is there a way to solve this?

Hi, welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Unclear.

You mention J24 in your first few examples.
But you haven't listed it in your final output.
Where would J24 go ?
QUESTION: If you had J2 where would J2 go in relation to J24 ?

Output appears to be sort by:
- first alphabetic letter
- then if value has a "-" sort by highest number otherwise sort by lowest number

Hi anon12398,

You could split each character using the 'Text to Columns' feature under the 'Data' tab and then do a Custom sort excluding the '-' character.
This a bit manual though, if you have lots of rows you could use a Macro if you're familiar with VBA?

Where would an F-2 go in relation to F-2B ?

Where would an F-2 go in relation to F-2B ?

without trailing letter comes before a trailing letter

Please Login or Register  to view this content.

OK, this works on your example data, and should work for any number in the string up to 100,000.

Create a Helper column with this formula

=LEFT(A1,1)&IF(MID(A1,2,1)="-",0,1)&100000-SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)) * ROW(INDIRECT("1:"&LEN(A1))), 0), ROW(INDIRECT("1:"&LEN(A1))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A1)))/10)

and sort the data on the helper column

Formula sorts on
first letter
then a 1 or 0 (0 if next character is "-" otherwise 1
then the number that follows - this is subtracted from 100,000 to provide a reverse order a-3 a-2 a-1 rather than a-1 a-2 a-3


Not sure about f-2 and f-2b being in right order though if they were to exist together.

UPDATE: OOps! Doesn't work! Thinking...

This looks better.

=LEFT(A1,1)&IF(MID(A1,2,1)="-",0,1)&100000+IF(MID(A1,2,1)="-",-1,1)*SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)) * ROW(INDIRECT("1:"&LEN(A1))), 0), ROW(INDIRECT("1:"&LEN(A1))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A1)))/10)

I still havent sorted
F-2 F-2B

F-2 F-2b problem sorted

=LEFT(A1,1)&IF(MID(A1,2,1)="-",0,1)&100000+IF(MID(A1,2,1)="-",-1,1)*SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)) * ROW(INDIRECT("1:"&LEN(A1))), 0), ROW(INDIRECT("1:"&LEN(A1))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A1)))/10)&(RIGHT(A1,1)>="A")*1

This assumes trailing letters will actually be letters not punctuation characters
and you wont have

F-2b and F-2B together