SOLVED

Excel Text Sorting

%3CLINGO-SUB%20id%3D%22lingo-sub-1504479%22%20slang%3D%22en-US%22%3EExcel%20Text%20Sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1504479%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20to%20sort%20a%20column%20of%20text%20and%20ignore%20any%20leading%20%22A%22%20or%20%22The%22%20in%20the%20text%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1504479%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1504498%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Text%20Sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1504498%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F718720%22%20target%3D%22_blank%22%3E%40DarkPalidin%3C%2FA%3E%26nbsp%3B%2C%20if%20you%20have%20the%20new%20dynamic%20array%20functions%2C%20this%20will%20work.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20372px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F203325i07458AE1ABCC652C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1504531%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Text%20Sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1504531%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20modify%20a%20bit%20since%20formula%20not%20only%20sort%20but%20change%20the%20source%20text%20as%20well%2C%20plus%20give%20an%20error%20for%20such%20words%20as%20Apple%20not%20necessary%20each%20sentence%20starts%20from%20The%20or%20A.%3C%2FP%3E%0A%3CP%3ESomething%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSORTBY(A3%3AB14%2C%0A%20%20%20%20%20IFS(%0A%20%20%20%20%20%20%20%20%20LEFT(A3%3AA14%2C4)%3D%22The%20%22%2CRIGHT(A3%3AA14%2CLEN(A3%3AA14)-4)%2C%0A%20%20%20%20%20%20%20%20%20LEFT(A3%3AA14%2C3)%3D%22An%20%22%2C%20RIGHT(A3%3AA14%2CLEN(A3%3AA14)-3)%2C%0A%20%20%20%20%20%20%20%20%20LEFT(A3%3AA14%2C2)%3D%22A%20%22%2C%20%20RIGHT(A3%3AA14%2CLEN(A3%3AA14)-2)%2C%0A%20%20%20%20%20%20%20%20%20TRUE%2C%20A3%3AA14%0A))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIf%20Excel%20doesn't%20support%20dynamic%20arrays%2C%20add%20helper%20column%20with%20formula%20as%20IFS(...)%20above%20and%20sort%20by%20it.%20If%20IFS()%20is%20not%20available%20when%20use%20nested%20IF().%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1504534%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Text%20Sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1504534%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%2C%20nicely%20done.%20Quick%20question%20%3A%20How%20do%20you%20post%20the%20color%20coded%20formula%20on%20here%3F%20Also%2C%20do%20you%20format%20your%20fomulas%20like%20this%20in%20Excel.%20Looks%20very%20clean%20and%20easy%20to%20read.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Is there a way to sort a column of text and ignore any leading "A" or "The" in the text?

 

Thanks!

7 Replies
Highlighted
Solution

@DarkPalidin , if you have the new dynamic array functions, this will work.

image.png

Highlighted

@TheAntony 

I'd modify a bit since formula not only sort but change the source text as well, plus give an error for such words as Apple not necessary each sentence starts from The or A.

Something like

=SORTBY(A3:B14,
     IFS(
         LEFT(A3:A14,4)="The ",RIGHT(A3:A14,LEN(A3:A14)-4),
         LEFT(A3:A14,3)="An ", RIGHT(A3:A14,LEN(A3:A14)-3),
         LEFT(A3:A14,2)="A ",  RIGHT(A3:A14,LEN(A3:A14)-2),
         TRUE, A3:A14
))

If Excel doesn't support dynamic arrays, add helper column with formula as IFS(...) above and sort by it. If IFS() is not available when use nested IF().

Highlighted

@Sergei Baklan , nicely done. Quick question : How do you post the color coded formula on here? Also, do you format your fomulas like this in Excel. Looks very clean and easy to read.

Highlighted

@TheAntony , thank you

Here if replying on the post you use 

you may select which language use for formatting

image.png

and it adds some colors to the formulas after you post the reply (not during replying).

 

In Excel - nope, I don't use any formatters. Just Alt+Enter in formula bar or copy/paste to Notepad++ to format relatively big formulas and after that copy/paste back.

Highlighted

@TheAntony In previous message one screenshot disappeared and I can't insert it back:

 

image.png

Highlighted

@Sergei Baklan , that's a neat feature. Thank you!

I'm learning a lot from your solutions. Thanks!

Highlighted
Exxcellent!, I can work with this.
duhg