Jul 04 2020 11:32 AM
Is there a way to sort a column of text and ignore any leading "A" or "The" in the text?
Thanks!
Jul 04 2020 12:21 PM
Solution@DarkPalidin , if you have the new dynamic array functions, this will work.
Jul 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.
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().
Jul 04 2020 01:53 PM
@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.
Jul 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.
Jul 04 2020 03:43 PM
Jul 04 2020 03:46 PM - edited Jul 04 2020 03:46 PM
@Sergei Baklan , that's a neat feature. Thank you!
I'm learning a lot from your solutions. Thanks!
Jul 04 2020 12:21 PM
Solution@DarkPalidin , if you have the new dynamic array functions, this will work.