Forum Discussion
Solved
Excel Text Sorting
Is there a way to sort a column of text and ignore any leading "A" or "The" in the text? Thanks!
DarkPalidin , if you have the new dynamic array functions, this will work.
DarkPalidin , if you have the new dynamic array functions, this will work.
- Exxcellent!, I can work with this.
duhg 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().
SergeiBaklan , 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.
