How can I sort column of combined text & numbers

[dbe594]

I have a column of data I'm having trouble sorting....

DGPF45

DGPF450

DGPF51

DGPF511

The smaller numbers are mixed in with the larger numbers in the wrong places, rather than two-digit numbers being sorted first, and then three-digit numbers...

DGPF45

DGPF51

DGPF450

DGPF511

The text always comes first and ends with the "F" character. This should also be able to sort single number values (e.g., DGPF1, DGPF2...DGPF10, DGPF11...DGPF100, DGPF101). I believe our numbers could go as high as DGPF1000.

I know I'm looking at a formula for a "sort column," but I am not getting it right. Would love help with this!

 

[Rick Rothstein]

One possible way is to use this array-entered** formula to create the helper column for your sort...

=TEXT(RIGHT(A1,COUNT(-RIGHT(A1,ROW($1:$9)))),"000000000")

**Commit this formula using CTRL+SHIFT+ENTER and note just Enter by itself

 

[jasonb75]

Assuming that there is only ever 1 "F" in the text and that the numeric part is never more than 5 digits.

=LEFT(A2,FIND("F",A2))&TEXT(MID(A2,FIND("F",A2)+1,5)+0,"00000")

I've assumed from the wording of your post that the number of letters in the text can vary, if it is always 4 letters ending in F then it can be simplified to something like

=LEFT(A2,4)&TEXT(MID(A2,5,5)+0,"00000")

 

[JEC]

As a formula, since you have office 365

=SORTBY(A1:A4,--MID(A1:A4,SEARCH("F",A1:A4)+1,LEN(A1:A4)),1)

 

[dbe594]

One possible way is to use this array-entered** formula to create the helper column for your sort...

=TEXT(RIGHT(A1,COUNT(-RIGHT(A1,ROW($1:$9)))),"000000000")

**Commit this formula using CTRL+SHIFT+ENTER and note just Enter by itself

This worked nicely! Thank you!! :D

 

[dbe594]

One possible way is to use this array-entered** formula to create the helper column for your sort...

=TEXT(RIGHT(A1,COUNT(-RIGHT(A1,ROW($1:$9)))),"000000000")

**Commit this formula using CTRL+SHIFT+ENTER and note just Enter by itself

Oh Oh. We threw a wrench into the works. Now we have alpha characters after the numeric...

CLVF510

CLVF511

CLVF512

CLVF513

CLVF1A

CLVF1B

CLVF1C

CLVF1D

CLVF2A

CLVF2B

CLVF4Z

Is there any way to adjust the formula to adjust for this?

 

[Rick Rothstein]

Oh Oh. We threw a wrench into the works. Now we have alpha characters after the numeric...

Is there any way to adjust the formula to adjust for this?

Your examples all show a single letter (when there is text after the number)... will it always be a single trailing letter (when there is one) or could it be more than one letter?

 

[dbe594]

Your examples all show a single letter (when there is text after the number)... will it always be a single trailing letter (when there is one) or could it be more than one letter?

Ha! The way its going I wouldn't put it past them! But I think we'll have the one letter for a while anyway.

 

[Rick Rothstein]

Since you are using XL365, and as long as the only numbers in the cell are the ones you are trying to retrieve, then this should work no matter how many letters are on the end...

=CONCAT(IFERROR(0+MID(A1,SEQUENCE(LEN(A1)),1),""))

The above formula returns the number as text in order to preserve any leading zeros that might be in number. If that is not a concern and you want real numbers returned, then use this instead...

=0+CONCAT(IFERROR(0+MID(A1,SEQUENCE(LEN(A1)),1),""))