Extracting prices from a string of text

If I have a string of text in cell A1 as follows...

"Asfsdfsdf (£30) - 3 x gfgdfgfdgfdgf, £4.50 dgfdgfdg"

... Is it possible to extract the 2 prices (both preceded by "£"), add them together, and put the total in cell B1? (in this case resulting in £34.50 being displayed in cell B1)

Issues that are making this formula tricky to work out as follows....
- Sometimes the price has no decimal places and sometimes the price has 2 decimal places.
- Sometimes the price is immediately followed by a closed bracket ")" rather than a space " ".
- Sometimes there are other numbers in the string that aren't a price - in this case there is a number 3 in cell A1 which needs ignoring as it is not a price.

Is a VBA solution acceptable?

In case no Formula comes Up

Please Login or Register  to view this content.

Kind regards
Leo

AlKey that's gold!

Originally Posted by AlKey

With formula

Formula:

Please Login or Register  to view this content.

v	A	B
1	Asfsdfsdf (£30) - 3 x gfgdfgfdgfdgf, £4.50 dgfdgfdg	34.5

Originally Posted by FlameRetired

For formula solution.

Formula:

Please Login or Register  to view this content.

It assumes a lot.

Thanks for the replies. These are both great and work perfectly with the string I provided "Asfsdfsdf (£30) - 3 x gfgdfgfdgfdgf, £4.50 dgfdgfdg".... However, I should have mentioned that sometimes there is only one price in the string and sometimes there are 3 prices in the string... eg."asfsad £5 sdfsdf" or "asdfsadf 3 ewewsafdaf £10 sadfdsf £3.30 sfdsfds 2 £7"

Is there a formula that can do this for any number of prices in the string?

Originally Posted by LeoTaxi

In case no Formula comes Up

Please Login or Register  to view this content.

Kind regards
Leo

Thanks for this as well... I've just tested it and it's almost working perfectly, however, it seems to have problems with a few strings...

Eg 1. "2 hours waiting time (£30)" = 288 but is should be 30
Eg 2. "2 x Car Wash's = £10" = 286 but it should be 10
Eg 3. "2hrs waiting (£30)" = 312 but it should be 30

As long as the price is preceeded by "£" sign.. the code below should match your request.

Please Login or Register  to view this content.

Originally Posted by AlKey

Here is another way
Enter formula in B1 and copy across and then down.

Formula:

Please Login or Register  to view this content.

v	A	B	C	D	E
1	Asfsdfsdf (£30) - 3 x gfgdfgfdgfdgf, £4.50 dgfdgfdg	£30.00	£4.50
2	Asfsdfsdf (£30) - 3 x gfgdf(£25.6)gfdgfdgf, £4.50 dgfdgfdg	£30.00	£25.60	£4.50
3	Asfsdfsdf (£30) - 3 x gfgdf(£25.6)gfdgfd £5.75 gf, £4.50 dgfdgfdg	£30.00	£25.60	£5.75	£4.50

Awesome.... this does the job thanks very much..... thanks to everyone for your replies.