Difficulty with finding value after text in a cell and copy to next column

[Aisling 5C]

Hello,

I have various excel files with hundred of thousands of lines from which I am trying to extract data from a very long text string to paste it in the next column.

For the record, here's what the cell look like :

Please Login or Register  to view this content.

Those are basically trade data between 2 assets, the 1.3.1570 and the 1.3.3467 and are switching position depending on if it was a buy or a sale.

I'm trying to get the amount of each asset copy/pasted in the next corresponding cell. For example, row B should represent the quantity of 1.3.1517 and row C the one for 1.3.3467
How do I extract those number coming after the "pays":{"amount":" ? I've tried basic solution other post made but the quotation mark got the best out of me. I'd also need the result as a numeric value so that I can do further maths with it. I also am looking at having the result either positive or negative depending on if it was a "pays" or "receives"

Many thanks all!

[CRIMEDOG]

IF the data is in COlumn B (starting at B2)
And you need the info in column C (C2)
Add this to C2 =MID($B2,SEARCH("pays",$B2)+17,(SEARCH("Asset_ID",$B2,SEARCH("pays",$B2)+17)-3)-(SEARCH("pays",$B2)+17))

the 17 represents count of spaces between A In Asst ID to the first number so it needs to be pretty uniform information.

You can surround the formula with Value()
=VALUE(MID($B2,SEARCH("pays",$B2)+17,(SEARCH("Asset_ID",$B2,SEARCH("pays",$B2)+17)-3)-(SEARCH("pays",$B2)+17)))

[AlKey]

To get "Pays" value:
Enter formula in cell B2

Please Login or Register  to view this content.

To get these values: 1.3.1570 and 1.3.3467
Enter formula in cell C2 and drag formula across

Please Login or Register  to view this content.

	A	B	C	D	E	F
1	Text string	Pays
2	[4,{"fee":{"amount":6823,"asset_id":"1.3.1570"},"order_id":"1.7.182934669","account_id":"1.2.766653","pays":{"amount":"13275511063","asset_id":"1.3.3467"},"receives":{"amount":3411806,"asset_id":"1.3.1570"},"fill_price":{"base":{"amount":"31060158767","asset_id":"1.3.3467"},"quote":{"amount":7982460,"asset_id":"1.3.1570"}},"is_maker":true}]	13275511063	1.3.1570	1.3.3467	1.3.1570	1.3.3467

Sheet: Sheet1