vba help - extract data from text value via Regular expresion

[Mallesh23]

Hi Team,

I want to extract no of records and Amount value from text file via regular expression.


Sub-Total Product X3 Rejected : No of Records = 0000 Amount = 0.00+
Generated : No of Records = 0008 Amount = 65,000-
Received : No of Records = 0009 Amount = 75,000-



Sub-Total Product X4 Rejected : No of Records = 0012 Amount = 0.00+
Generated : No of Records = 0016 Amount = 15,000-
Received : No of Records = 0019 Amount = 25,000-




expected output after Reading Text value

	Records	Amount
Rejected	0	0
Generated	8	-65000
Received	9	-75000
Rejected	12	0
Generated	16	15000
Received	19	14000

thanks
mg

 

[Sufiyan97]

Try

Book1
	F	G	H	I
2	Sub-Total Product X3 Rejected : No of Records = 0000 Amount = 0.00+
3	Generated : No of Records = 0008 Amount = 65,000-
4	Received : No of Records = 0009 Amount = 75,000-
5
6
7
8	Sub-Total Product X4 Rejected : No of Records = 0012 Amount = 0.00+
9	Generated : No of Records = 0016 Amount = 15,000-
10	Received : No of Records = 0019 Amount = 25,000-
11
12
13		Records	Amount
14	Rejected	0000	0.00
15	Generated	008	-65,000
16	Received	0009	-75,000
17	Rejected	0012	0.00
18	Generated	016	-15,000
19	Received	0019	-25,000
20
21
Sheet1

Cell Formulas
Range	Formula
G14:G16	G14	=TRIM(LEFT(SUBSTITUTE(MID(F2,FIND(F14,F2)+LEN(CONCATENATE(F14," : No of Records = ")),99)," ",REPT(" ",99)),9))
H14	H14	=TRIM(LEFT(SUBSTITUTE(MID(F2,FIND(" Amount = ",F2)+LEN(" Amount = "),99),"+",REPT(" ",99)),99))
H15:H16	H15	=IF(ISNUMBER(SEARCH("-",TRIM(LEFT(SUBSTITUTE(MID(F3,FIND(" Amount = ",F3)+LEN(" Amount = "),99),"+",REPT(" ",99)),99)))),"-"&TRIM(LEFT(SUBSTITUTE(MID(F3,FIND(" Amount = ",F3)+LEN(" Amount = "),99),"-",REPT(" ",99)),99)),TRIM(LEFT(SUBSTITUTE(MID(F3,FIND(" Amount = ",F3)+LEN(" Amount = "),99),"+",REPT(" ",99)),99)))
G17:G19	G17	=TRIM(LEFT(SUBSTITUTE(MID(F8,FIND(F17,F8)+LEN(CONCATENATE(F17," : No of Records = ")),99)," ",REPT(" ",99)),9))
H17:H19	H17	=IF(ISNUMBER(SEARCH("-",TRIM(LEFT(SUBSTITUTE(MID(F8,FIND(" Amount = ",F8)+LEN(" Amount = "),99),"+",REPT(" ",99)),99)))),"-"&TRIM(LEFT(SUBSTITUTE(MID(F8,FIND(" Amount = ",F8)+LEN(" Amount = "),99),"-",REPT(" ",99)),99)),TRIM(LEFT(SUBSTITUTE(MID(F8,FIND(" Amount = ",F8)+LEN(" Amount = "),99),"+",REPT(" ",99)),99)))

 

[Domenic]

For your regular expression, try the following matching pattern...

=\s?(\d{4}).*=\s?((?:\d{1,3},)*(?:\d{1,3})(?:\.00)?[-+])

For an example and explanation, see the following link...

regex101: build, test, and debug regex

Regular expression tester with syntax highlighting, explanation, cheat sheet for PHP/PCRE, Python, GO, JavaScript, Java, C#/.NET, Rust.

regex101.com

Hope this helps!