Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 28, 2022 01:25 AM
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?
Solved! Go to Solution.
Mar 28, 2022 03:02 AM
I think this Regex will help: REGEX_EXTRACT(Field name, "^\\D*")
Thanks to @Justin_Barrett and his intro to Regex :heart_eyes:
Mar 28, 2022 03:02 AM
I think this Regex will help: REGEX_EXTRACT(Field name, "^\\D*")
Thanks to @Justin_Barrett and his intro to Regex :heart_eyes:
Mar 28, 2022 03:25 AM
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
Mar 28, 2022 06:57 AM
: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
Mar 28, 2022 07:18 AM
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.
Mar 28, 2022 10:20 AM
This one can be useful too: Introduction to Regular Expressions by Codecademy