Hi! I’m looking for a formula that will remove specific words from a multi-word column.
- 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.