Forum Discussion
Excel Text Sorting
DarkPalidin , if you have the new dynamic array functions, this will work.
DarkPalidin , if you have the new dynamic array functions, this will work.
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.
TheAntony , thank you
Here if replying on the post you use
you may select which language use for formatting
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.
SergeiBaklan , that's a neat feature. Thank you!
I'm learning a lot from your solutions. Thanks!