Extracting numeric ID from URL using Airtable regex functions

Other Questions Airtable
1

Hey everyone! I’m trying to figure out how to use Airtable’s regex functions to grab just the numeric ID from a URL. Here’s what I’m working with:

Target Audience field:
url = https://example-store.com/segment?id=9876543210

I only want to extract the ‘9876543210’ part. I’ve tried this formula:

IF(
  REGEX_MATCH({Audience URL}, '=\d+'),
  SUBSTITUTE(
    REGEX_EXTRACT({Audience URL}, '=\d+'),
    '=',
    ''
  )
)

But it’s not quite right. Some URLs have extra stuff after the ID, like:

https://example-store.com/segment?id=9876543210&tag=promo&source=email

Any ideas on how to make this work? I’m kinda new to regex, so any help would be awesome. Thanks!

2

I’ve encountered this issue in my projects as well. Here’s a robust solution that should work for your case:

REGEX_EXTRACT({Audience URL}, ‘id=(\d+)’)

This pattern captures the digits immediately following ‘id=’ without being affected by additional parameters. It’s more reliable than using lookaheads, which can be tricky in some regex implementations.

If you need to handle potential variations in case, you could modify it to:

REGEX_EXTRACT({Audience URL}, ‘(?i)id=(\d+)’)

The (?i) flag makes the match case-insensitive. This approach has served me well across various URL structures. Remember to test it thoroughly with your specific dataset to ensure it covers all your use cases.

3

hey there! i’ve used regex for url parsing before. try this formula:

REGEX_EXTRACT({Audience URL}, ‘id=(\d+)’)

it grabs just the numbers after ‘id=’ no matter what comes after. works great for me with all kinds of weird urls. lemme know if u need any more help!

4

I’ve dealt with a similar issue before, and I can share what worked for me. Instead of using ‘=\d+’, try using ‘(?<=id=)\d+’ in your regex pattern. This looks for digits that come right after ‘id=’, which should solve your problem with extra parameters.

Here’s an updated formula you can try:

REGEX_EXTRACT({Audience URL}, ‘(?<=id=)\d+’)

This should extract just the numeric ID, regardless of what comes after it in the URL. It worked well for me when I had to parse various types of URLs in Airtable.

One thing to keep in mind: make sure your ‘id=’ is always in lowercase in the URLs. If it might be capitalized sometimes, you might need to adjust the regex to account for that. Let me know if this helps or if you need any clarification!