07-04-2020 11:32 AM
07-04-2020 01:41 PM
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.
=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().
07-04-2020 03:41 PM
@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.
by Carlo74 on June 26, 2020
by Prash Shirolkar on September 25, 2018
by Blake T Walsh on August 23, 2018