Searching for a term in a cell

Question

I am trying to extract my bank balance number from an email into Google Spreadsheets. So far I have a Google script grabbing the email, then pasting it into a Google spreadsheet in rawcontent form. You can see below the RawContent that is being pasted. (This is not my bank balance or card it is a template that has been used and taken off my bank's website for a draft of what the email would look like.)

    MIME-Version: 1.0
    Received: by 10.79.112.196 with HTTP; Tue, 20 Oct 2015 18:13:30 -0700 (PDT)
    Date: Wed, 21 Oct 2015 02:13:30 +0100
    Delivered-To: liamnagle5@gmail.com
    Message-ID: 
    Subject: BB TEST
    From: Liam Nagle 
    To: Liam Nagle 
    Content-Type: multipart/alternative; boundary=001a114d944e1c2fac052293164b

    --001a114d944e1c2fac052293164b
    Content-Type: text/plain; charset=UTF-8
    Content-Transfer-Encoding: quoted-printable

    Dear Mr. Broe,

    Your weekly account summary for
    account ending 6920 is below:

    Current Balance as at 12:30 26 Sep:
    =C2=A32500.00+
    Available Balance as at 12:30 26 Sep:
    =C2=A32700.00+

    RECENT TRANSACTIONS:
    25Sep Transfer =C2=A310.59-
    25Sep Cheque deposit 150.00+
    24Sep Direct Debit =C2=A33.60-
    23Sep Standing order =C2=A323.50-
    21Sep CC payment =C2=A349.00-

    Regards,

    Santander Alerts

    --001a114d944e1c2fac052293164b
    Content-Type: text/html; charset=UTF-8
    Content-Transfer-Encoding: quoted-printable

    Dear Mr. Broe,

Your weekly acc=
    ount summary for
account ending 6920 is below:

Current Balance as at 12:30 26 Sep:=C2=A0
Available Balance as at 12:30 26 Sep:=C2=
    =A0
=C2=A32700.00+

RECENT TRA=
    NSACTIONS:
25Sep Transfer =C2=A310.59-
25=
    Sep Cheque deposit 150.00+
24Sep Direct Debit =C2=A33.60-<=
    /span>
23Sep Standing order =C2=A323.50-
21Sep C=
    C payment =C2=A349.00-

Regards,

Santander Alerts

    --001a114d944e1c2fac052293164b--

What I am hoping to get out of this is where it says:

    Current Balance as at 12:30 26 Sep:
    =C2=A32500.00+
    Available Balance as at 12:30 26 Sep:
    =C2=A32700.00+

From here i would like to extract the numbers 2500.00 and 2700.00. The =C2=A3 part is denoting a £ written in RawContent form. After extracting the two numbers I am hoping to assign these to two cells on another sheet to then use that data to work out financial plans etc.

Robin Gertenbach · Accepted Answer

You can use the formula =REGEXEXTRACT(E4,"=C2=A3(\d+\.\d{2})\+") to get the first match but the second will be a bit trickier. Since you are already using a script I would write another quick function to extract those values.

function getBalance(){
  var data = SpreadsheetApp.getActiveSheet().getRange(1,1).getValue();
  var re = /=C2=A3(\d+\.\d{2})\+/g;
  var results = [re.exec(data)[1], re.exec(data)[1]];
  return results;
}

This will get the text, extract the two available balances by identifying them with the raw £ prefix and return them in an array.

You can then even run =GETBALANCE() from the spreadsheet and it will give you the balances as output in two separate rows.

Searching for a term in a cell

Answers (1)

Related Questions