+2Hi,
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?
Best answer by Databaser
I think this Regex will help: REGEX_EXTRACT(Field name, "^\\D*")
Thanks to @Justin_Barrett and his intro to Regex :heart_eyes:
+25I think this Regex will help: REGEX_EXTRACT(Field name, "^\\D*")
Thanks to @Justin_Barrett and his intro to Regex :heart_eyes:
+2I 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
+1Superb 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
+21Superb 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
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.
+25The 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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.