- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 11, 2025 07:35 AM - edited Jan 11, 2025 07:36 AM
Hello,
I have a table that is synced with transactions from my bank account, but I need help with extracting data from a JSON string stored in the Notes (Long text) field.
An example of the JSON looks like this:
{
"transactionId": "2022142460021",
"endToEndId": "\/VS2501000545\/SS\/KS308",
"bookingDate": "2025-01-10",
"valueDate": "2025-01-10",
"transactionAmount": {
"amount": "-390.00",
"currency": "EUR"
},
"currencyExchange": [
{
"exchangeRate": "1.00000000",
"quotationDate": "2025-01-10"
}
],
"creditorAccount": {
"iban": "SK9111110000002303283006"
},
"debtorName": "COMPANY s.r.o.",
"debtorAccount": {
"iban": "SK8511000000002622224000",
"currency": "EUR"
},
"remittanceInformationUnstructured": "Platba faktury 2501000995",
"remittanceInformationStructured": "[{\"reference\": \"2501000995\", \"referenceType\": \"VariableSymbol\"}, {\"reference\": \"308\", \"referenceType\": \"ConstantSymbol\"}]",
"additionalInformation": "Platba 1111\/000000-2303283006",
"bankTransactionCode": "",
"creditorAgent": "UNCRSKBXXXX",
"debtorAgent": "TATRSKBXXXX",
"internalTransactionId": "",
"ext_nordigen_acc_id": "",
"pending": false
}
To identify payments in my Partner table using automation, I need to write formulas for two fields:
1.IBAN (but exclude mine, as for income, I am the creditor, and for outcome, I am the debtor).
2.Variable symbol.
I was using ChatGPT to help with the formulas:
For IBAN:
IF(
AND(
FIND("SK", {Notes}), // Ensure "SK" exists in the Notes field
NOT(FIND("SK8511000000002622224000", {Notes})) // Exclude your IBAN
),
REGEX_EXTRACT({Notes}, "(SK\\d{2}\\d{4}\\d{4}\\d{4}\\d{4}\\d{4})"), // Extract the first IBAN
BLANK() // Return blank if no IBAN is found or if your IBAN is found
)
For Variable symbol:
REGEX_EXTRACT({Notes}, "\"reference\": \"(\\d+)\".*\"referenceType\": \"VariableSymbol\"")
But neither of these formulas is working. 😐
Thank you!
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 11, 2025 08:49 PM
ChatGPT isn't very good at writing Airtable formulas. I'm not surprised that the formulas it produces don't work. The first formula that you posted isn't even a legal Airtable formula.
I would go about this with a series of formula fields.
First a formula to extract the creditorAccount to a {creditorAccount} field
IF(
REGEX_MATCH({Notes}, '"creditorAccount": {[^}]+}'),
REGEX_EXTRACT({Notes}, '"creditorAccount": {[^}]+}')
)
Then a formula to extract the IBAN from the creditorAccount info
IF(
REGEX_MATCH({creditorAccount}, '"iban": "\\w+"'),
REGEX_REPLACE(
REGEX_EXTRACT({creditorAccount}, '"iban": "\\w+"'),
'("iban": ")(\\w+)(")',
'$2'
)
)
To get the info for the debtorAccount, use the same formulas, but use "debtorAccount" instead of "creditorAccount".
Here are some other possible methods to get the data
- Use an AI Field to extract the data you want.
- Write a script that parses the JSON. (But if you use ChatGPT to write the script, you might have issues with the code not working either.)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 11, 2025 08:49 PM
ChatGPT isn't very good at writing Airtable formulas. I'm not surprised that the formulas it produces don't work. The first formula that you posted isn't even a legal Airtable formula.
I would go about this with a series of formula fields.
First a formula to extract the creditorAccount to a {creditorAccount} field
IF(
REGEX_MATCH({Notes}, '"creditorAccount": {[^}]+}'),
REGEX_EXTRACT({Notes}, '"creditorAccount": {[^}]+}')
)
Then a formula to extract the IBAN from the creditorAccount info
IF(
REGEX_MATCH({creditorAccount}, '"iban": "\\w+"'),
REGEX_REPLACE(
REGEX_EXTRACT({creditorAccount}, '"iban": "\\w+"'),
'("iban": ")(\\w+)(")',
'$2'
)
)
To get the info for the debtorAccount, use the same formulas, but use "debtorAccount" instead of "creditorAccount".
Here are some other possible methods to get the data
- Use an AI Field to extract the data you want.
- Write a script that parses the JSON. (But if you use ChatGPT to write the script, you might have issues with the code not working either.)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 12, 2025 02:06 AM
Thank you @kuovonne ! It works very perfectly.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 12, 2025 02:23 AM
@kuovonne could you please be so kind and help me with the Variable Symbol as well?
This should probably require another regex prompt. 😬
"remittanceInformationStructured": "[{\"reference\": \"20240001\", \"referenceType\": \"VariableSymbol\"}, {\"reference\": \"308\", \"referenceType\": \"ConstantSymbol\"}]",
It should return 2024001.
Thank you!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 13, 2025 08:25 AM
You can get the variable symbol using a variation of the technique I showed in my previous formula. Use a REGEX functions to narrow down the portion of text you want, using multiple fields as appropriate to make things easier to understand. The REGEX functions are documented in the formula field reference, which links to the syntax needed to create a regular expression. You can also use a REGEX tester when building out your expressions.
