Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Find initial letters of UK Post Code

Topic Labels: Formulas
Solved
Jump to Solution
2037 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Scott_Callery
6 - Interface Innovator
6 - Interface Innovator

Hi,

I am looking to find the initial letters of a UK post code before the first number.
UK Post codes start with 1 or 2 letters and are always numbers and then letters.
E.g. ‘WA1 1AB’ or ‘SK2 3LP’ or ‘L1 1AA’ or ‘M2 3NK’

I am looking for a formula to return the initial letters before the first number.
So for the above examples, I would expect ‘WA’, ‘SK’, ‘L’ and ‘M’

Any ideas?

1 Solution

Accepted Solutions
Databaser
12 - Earth
12 - Earth

Hi @Scott_Callery

I think this Regex will help: REGEX_EXTRACT(Field name, "^\\D*")

Thanks to @Justin_Barrett and his intro to Regex :heart_eyes:

See Solution in Thread

5 Replies 5
Databaser
12 - Earth
12 - Earth

Hi @Scott_Callery

I think this Regex will help: REGEX_EXTRACT(Field name, "^\\D*")

Thanks to @Justin_Barrett and his intro to Regex :heart_eyes:

Superb this does exactly what I need :partying_face:
Would you be able to explain what the "^\\D*" part does?
I looked at the Airtable formula page but it doesn’t give much explanation.
Thanks @Justin_Barrett

:wave: Hey there!

Regex is a scary monster even for those who work with it all day. It’s essentially a small coding language in and of itself used commonly within JavaScript to Match or manipulate strings of text.

Just a little regex knowledge can go a long way though! It’s definitely worth dipping your toes into

The caret (^) at the start tells the interpreter to only look for matches at the start of the string. The token \D matches any non-digit character, and the asterisk says to match the previous token zero or more times. In short, it matches all non-digit characters at the start of the string. Once a digit is found, the match stops.

FWIW the extra backslash before \D is required because the backslash in formula strings is an escape character. To ensure that the backslash before the D is maintained as part of that token, you need two backslashes, essentially escaping the escape character itself.

I recommend playing around on regex101.com if you want to learn more about Regex in a hands-on way. It’s not the only site where you can do this, but it’s my favorite so far. Just make sure that you set the “Flavor” option (upper left) to “Golang,” which is the closest Regex variant to what Airtable uses.

This one can be useful too: Introduction to Regular Expressions by Codecademy