Regular expression within a script to find a line without a certain word in it

  • Last Post 18 February 2020
David Sladdin posted this 18 February 2020

Good afternoon,


I have an invoice with a Sub Total line and a Total line. I would like to extract the total with your example script, part of which is shown below:


Dim Target, Target2

Target = ".*(A.B.N.|ABN |ABN|abn:|abn: |A.B.N )\$*(([A-Za-z]{1,2}[0-9]{4}))"

Target2 = ".*(TOTAL VALUE |\(?<!Sub\s\)Total £|SALE AMOUNT: |Amount Due: |Balance Due |Balance Due: |Invoice Total \(IncGST\)|Invoice Total \(IncGST\) |Total Amount Payable )\$*([0-9]+\.[0-9]{2})"


I want to be able to use regular expression to select the Total amount only, but I can't get it to work with the above regex code.


Here is an example of my OCRTEXT:


please remit to:- Trade UK, Selectapost 28, Sub Total ?10.50

Sheffolda, D44 3AA VAT ?2.10

Customer Contact Number 0345 123456 Total ?12.60


I am only getting the 10.50 amount if I just use Total

As you can see above I am trying to run \(?<!Sub\s\)Total 

to do a negative look behind, but this brings up nothing from my testing.


Please help!

Order By: Standard | Newest | Votes
luca.scarpati posted this 18 February 2020

Hi David,


your case is normal because RegExp is general maybe you can use some previous string as a control ... for example I see the word "Customer" on the same line, so you could use a "Target" RegExp similar to this:

.*(Customer[ A-z0-9]+Total[ ]*[\?]*[\$]*)\$*([0-9]+.[0-9]+)


I hope it will help


Best regards,


  • Liked by
  • David Sladdin
David Sladdin posted this 18 February 2020

Hi Luca,

Well that worked first time so thank you so much!


  • Liked by
  • luca.scarpati