Skip to main content

I have an interesting problem. I need to create database-sort-friendly versions of names, where all definite and indefinite articles are dropped from the beginning of each name.



I came up with this formula:



IF(REGEX_MATCH(Name,'^THE'),


	SUBSTITUTE(Name, LEFT(Name, 4), ''),


IF(REGEX_MATCH(Name, '^A'),


	SUBSTITUTE(Name, LEFT(Name, 2), ''),


IF(REGEX_MATCH(Name, '^AN'),


	SUBSTITUTE(Name, LEFT(Name, 3), ''),


	Name


)))


But it’s not working for names that start with “AN”. The formula is deleting all "AN"s.





Why is it working with “THE” and “A”, but not with “AN”?


What am I missing? I am at a loss.



Thank you in advance!

When you use the SUBSTITUTE function, you’re telling it to replace all occurrences of the matching phrase with a new phrase. So you’re telling it to replace multiple things at once.



Also, you don’t need REGEX functions for this.



What you really want to use is the RIGHT and LEN functions, to tell it to return only the rightmost part of the string. And you can use the LEFT function to search for what you’re looking for:



IF(


LEFT(Name,4)="THE ",RIGHT(Name,LEN(Name)-4),


IF(


LEFT(Name,2)="A ",RIGHT(Name,LEN(Name)-2),


IF(


LEFT(Name,3)="AN ",RIGHT(Name,LEN(Name)-3),


Name


)))


Note that Airtable will see the capitalization of “THE” differently than it sees the capitalization of “The”, so be sure to trap for different capitalizations if you need to.

When you use the SUBSTITUTE function, you’re telling it to replace all occurrences of the matching phrase with a new phrase. So you’re telling it to replace multiple things at once.



Also, you don’t need REGEX functions for this.



What you really want to use is the RIGHT and LEN functions, to tell it to return only the rightmost part of the string. And you can use the LEFT function to search for what you’re looking for:



IF(


LEFT(Name,4)="THE ",RIGHT(Name,LEN(Name)-4),


IF(


LEFT(Name,2)="A ",RIGHT(Name,LEN(Name)-2),


IF(


LEFT(Name,3)="AN ",RIGHT(Name,LEN(Name)-3),


Name


)))


Note that Airtable will see the capitalization of “THE” differently than it sees the capitalization of “The”, so be sure to trap for different capitalizations if you need to.




Thank you so much, @ScottWorld!



The LEN function is something I have a hard time wrapping my head around, but it’s beginning to make more and more sense now.



In your formula, when you use the RIGHT function, why are you using -3 for THE, -1 for A and -2 for AN? Doesn’t that leave out the extra space after each article?



Thank you so much, @ScottWorld!



The LEN function is something I have a hard time wrapping my head around, but it’s beginning to make more and more sense now.



In your formula, when you use the RIGHT function, why are you using -3 for THE, -1 for A and -2 for AN? Doesn’t that leave out the extra space after each article?


@ScottWorld - your formula does leave in an extra space at the beginning, which you don’t see in Airtable, but when you export the field as a CSV, it’s showing up.



If I do this, there is no extra space:



IF(


LEFT(Name,4)="THE ",RIGHT(Name,LEN(Name)-4),


IF(


LEFT(Name,2)="A ",RIGHT(Name,LEN(Name)-2),


IF(


LEFT(Name,3)="AN ",RIGHT(Name,LEN(Name)-3),


Name


)))

@ScottWorld - your formula does leave in an extra space at the beginning, which you don’t see in Airtable, but when you export the field as a CSV, it’s showing up.



If I do this, there is no extra space:



IF(


LEFT(Name,4)="THE ",RIGHT(Name,LEN(Name)-4),


IF(


LEFT(Name,2)="A ",RIGHT(Name,LEN(Name)-2),


IF(


LEFT(Name,3)="AN ",RIGHT(Name,LEN(Name)-3),


Name


)))

Right, you caught my formula while I was still working on it. I posted it before I finished refining it. 😝 You can see the correct numbers in my message above.

Right, you caught my formula while I was still working on it. I posted it before I finished refining it. 😝 You can see the correct numbers in my message above.


Thanks again, @ScottWorld . I really appreciate your help!

Reply


Terms & Conditions