Spreadsheets

Parse a pasted bank SMS alert into columns in Google Sheets

Parse a pasted bank debit SMS into amount, date and merchant columns in Google Sheets with REGEXEXTRACT, and see why alert formats differ from bank to bank.

Editorial illustration for Parse a pasted bank SMS alert into columns in Google Sheets

If you paste bank alert messages into Google Sheets, one per row, REGEXEXTRACT can pull the amount, the date and the merchant into separate columns. The patterns below fit one made-up layout; your bank's wording will differ, so treat them as a pattern to adapt, not a finished tool.

What does the sample alert look like?

A hypothetical alert, pasted into A2:

  • Rs 1,234.50 debited from A/c XX1234 on 12-05-25 at SWIGGY. Avl bal Rs 8,000.00.

Keep the account number masked, as it is in the alert, and avoid sharing the sheet widely, since alerts can include balances.

How do I extract the amount?

  • =VALUE(SUBSTITUTE(REGEXEXTRACT(A2, "(?i)(?:rs\.?|inr)\s?([0-9,]+(?:\.[0-9]{1,2})?)"), ",", ""))

Reading the pattern: (?i) ignores capitals; (?:rs\.?|inr) matches Rs, Rs. or INR without capturing; \s? allows one space; and the bracketed part captures digits and commas with an optional decimal part. For the sample it returns 1,234.50 as text, SUBSTITUTE removes the comma, and VALUE turns 1234.50 into a number. REGEXEXTRACT returns the first match, which here is the debit amount and not the balance, because the balance comes later in the message. If your alert gives the balance first, the pattern will pick the wrong figure.

How do I extract the date?

Put the date text in C2 and a real date in D2:

  • C2: =REGEXEXTRACT(A2, "on ([0-9]{2}-[0-9]{2}-[0-9]{2,4})")
  • D2: =DATE(2000+VALUE(RIGHT(C2,2)), VALUE(MID(C2,4,2)), VALUE(LEFT(C2,2)))

C2 gives 12-05-25. D2 reads day from the left two characters, month from the middle two, and year from the right two, adding 2000, so it becomes 12 May 2025. If your bank prints a four-digit year, use VALUE(RIGHT(C2,4)) and drop the 2000. If it prints the month as a name, the pattern needs to change.

How do I extract the merchant?

  • =REGEXEXTRACT(A2, " at ([^.]+)\.")

This captures everything after the word at up to the next full stop, so the sample returns SWIGGY. It fails if the merchant name contains a full stop or the sentence order differs.

How do I tell a debit from a credit?

  • =IF(REGEXMATCH(A2, "(?i)debited"), -1, 1) * amount_cell

REGEXMATCH returns TRUE when the word debited appears. Debits become negative and credits positive, which suits a ledger. Replace amount_cell with the cell holding the amount.

ColumnHoldsFormula idea
APasted alertRaw text
BAmountREGEXEXTRACT plus SUBSTITUTE plus VALUE
CDate textREGEXEXTRACT on the on dd-mm-yy part
DReal dateDATE with LEFT, MID, RIGHT
EMerchantREGEXEXTRACT after at

A caution on privacy

Do not paste anything that grants access to your account. Alerts with one-time codes should never be put into a sheet or shared. Keep the sheet private.

When does this stop being practical?

How do I do many messages at once?

Paste one alert per row and wrap each formula in ARRAYFORMULA with IFERROR, so a row that does not match shows Check manually instead of an error. For example, ARRAYFORMULA(IFERROR(REGEXEXTRACT(A2:A200, " at ([^.]+)\."), "Check manually")) fills the merchant column for 199 rows in one go. Then sort or filter on Check manually to see which alerts use a different wording, and write a second pattern for those. Credit alerts use different words, such as credited, so test them separately.

Pasting messages one by one and fixing patterns for each bank is slow. Statements usually hold the same transactions in a cleaner form. SpendixAI does not read SMS; it takes statements you upload as PDF, CSV or Excel, and you review the rows before they are saved.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports