Extract number from text string

Hi all

Stumped by this one. I need to extract the 'amount' number from the text strings below. The format varies but there is consistency in that the number i need always follows:

"amount":"

here's a sample of the text string variance i'm working with:

{"amount":"100.0","currency":"sek","customerId":""}
{"currency":"EUR","customerId":"","amount":"5.0"}
{"currency":"GBP","amount":"20.0","customerId":""}
{"currency":"RON","amount":"50.0","customerId":""}
{"currency":"SEK","customerId":"","amount":"800.0"}
{"customerId":"","currency":"GBP","amount":"8.0"}
{"customerId":"","currency":"SEK","amount":"1000.0"}
{"customerId":"","currency":"SEK","amount":"300.0"}
{"customerId":"106513976","currency":"DKK","amount":"150.0"}
{"customerId":"3111","currency":"SEK","amount":"100.0"}

However, as you can see the number is not fixed digit length nor consistent position within the text string.

Help appreciated.

Assuming text string is in E2:

=LEFT(MID(E2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},E2&"0123456789")),25),FIND(CHAR(34),MID(E2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},E2&"0123456789")),25))-1)

Does this work for you?

Close! It works fine except the two last lines in my example i.e. the text strings that also contain a 'CustomerId'. In those instances the formula outputs the customer iD, not the amount.

If it makes things easier, i'm happy to use helper columns

cheers

Close! It works fine except the two last lines in my example i.e. the text strings that also contain a 'CustomerId'. In those instances the formula outputs the customer iD, not the amount.

If it makes things easier, i'm happy to use helper columns

cheers

Marvin and Tony,

These both work perfectly. Thanks ever so much