Extract "The" from a company name for alphabetization

1

Hi! I’m looking for a formula that will remove specific words from a multi-word column.

Use case:

  • I want to alphabetize this list of company names
  • Some of the names include words I would not want to be a factor in alphabetization (i.e., “the” or “a” or “of”)
  • I also want the user to be able to create an alternate alpha-by, manually, in case the automatically generated one doesn’t work (example: “The Warren Buffett Fund” should be alpha’d by “buffett-fund”, not “warren-buffett-fund”

Here are the columns I’ve created:

  • Original Name - full name of company
  • Split Name - separates the words in the “Original Name” column
  • Custom Alpha-By - manually entered by user if they don’t like the red “ALPHA-BY” column
  • ALPHA-BY (red) - joins the words in the “Split Name” column, and if there is something in the “Custom Alpha-By” column, replaces the auto-generated alpha-by with the custom one.
  • Desired Alpha-By (yellow) - this is just to show you want to appear in the red “ALPHA-BY” column. I wouldn’t included this in the real scenario.

Here are my formulas:

2

Hi @Kelly_Claus ,
I asked for access to your doc.

In the meantime, you might find something that could go very closer to your needs with this:

Feel free to ask if you have any question.

3

Hi @Kelly_Claus ,
maybe in your case a simple RegexReplace() (already provided in your doc) would work.

Let me know if you need any further clarification.
Cheers!

4

Oh nice! Thank you @Federico_Stefanato!!

1 Like