How to search Airtable records when users enter partial hyphenated last names

Other Questions Airtable
1

I have a wedding guest database in Airtable where some guests have hyphenated surnames like smith-johnson. When people fill out my form, they sometimes only enter the first part of their last name like smith instead of the full smith-johnson.

I’m trying to create a filter that can match partial surnames against the full hyphenated ones in my database. I get the firstName and lastName values from my form submission and want to query the Airtable base using their JavaScript SDK.

I attempted using a combination of REGEX functions with filterByFormula but it’s not working:

`(AND({firstName} = "${firstName}",FIND(REGEX_REPLACE("${lastName}",'[^-]*','' ),{lastName})>0))`

The issue is that when someone enters just smith, my query should still find the record with smith-johnson in the surname field. What’s the best approach to handle this partial matching scenario in Airtable? Any suggestions would be helpful.

2

just use FIND with the hyphen as delimiter. split on ‘-’ first, then check each part: FIND("${lastName}", LEFT({lastName}, FIND("-", {lastName})-1)). you’ll only match the first part of hyphenated names instead of random middle stuff. works great - i use it for my venue bookings db.

3

Had the same issue with a client directory. REGEX gets messy quick in Airtable - their formula system just isn’t built for it. I switched to FIND and it worked way better: (AND({firstName} = "${firstName}", FIND("${lastName}", {lastName}) > 0)). FIND searches anywhere in the hyphenated name, so “smith” matches “smith-johnson” since it finds “smith” at position 1. Way more reliable than trying to parse regex in Airtable. One heads up though - this catches partial matches in the middle too, so “john” would match “smith-johnson” which you probably don’t want. If that becomes a problem, split your surname field into two columns for each part of the hyphenated name and query both separately.

4

The wildcard approach works great here. Skip the position functions and just use an asterisk wildcard in your filterByFormula:

`(AND({firstName} = "${firstName}", {lastName} LIKE "${lastName}*"))`

This matches any surname starting with the entered text - “smith” will catch “smith-johnson” automatically. I used this for a conference registration system where people kept shortening their hyphenated names.

LIKE with wildcards beats regex parsing in Airtable. It handles partial matching without worrying about hyphen positions or complex string stuff. Also performs better than nested FIND operations on larger datasets.

Just remember it’s case-sensitive. You might want to convert input and database values to uppercase if case variations cause problems.

5

Skip the complex formula approach entirely. I hit this exact issue building an employee directory and found something way simpler.

Use SEARCH instead of FIND - it’s case insensitive and handles partial matching perfectly:

`(AND({firstName} = "${firstName}", SEARCH("${lastName}", {lastName}) >= 1))`

SEARCH returns the position where it finds the match, so >= 1 catches any match from the start. This handles “smith” finding “smith-johnson” without regex headaches.

Want to match only from the start of surnames (not middle parts)? Add a space check:

`(AND({firstName} = "${firstName}", OR(SEARCH("${lastName}", {lastName}) = 1, SEARCH("${lastName}", {lastName}) = SEARCH("-${lastName}", {lastName}) + 1)))`

This catches both cases - lastName at the very beginning OR right after a hyphen.

This video breaks down Airtable’s capabilities really well if you want more options:

I’ve used this approach across multiple projects and it’s rock solid. Way less frustrating than wrestling with Airtable’s regex limitations.

6

STARTSWITH is perfect for this. I had the same issue with a membership database full of hyphenated names. Skip the FIND/SEARCH headaches and use:

`(AND({firstName} = "${firstName}", OR(STARTSWITH({lastName}, "${lastName}"), STARTSWITH({lastName}, CONCATENATE("${firstName}", "-")))))`

Wait, this is cleaner:

`(AND({firstName} = "${firstName}", STARTSWITH({lastName}, "${lastName}")))`

STARTSWITH only matches from the start, so “smith” finds “smith-johnson” but won’t grab “johnson-smith” by mistake. Way cleaner than position checking and handles case sensitivity automatically. I’ve used this for two years with zero weird edge cases.