Text Extraction Formula

[Arts]

Hi all

I have text that needs to be extracted from a cell and did come close but can't quite get there.

There is text in a cell and what I need to do is extract the pound amount that has been mentioned so for example

If I have as text "the board approves £657k though no more..." I would like to attain the £657k. There is always a £ sign and then the amount.

What I manged was =MID(C4,SEARCH("£",C4),7) but the issue here is I have added 7 as a guesstimate and this sometimes brings in additional letters as in the first result I have £657k t. If I could get it to stop at the space after the amount so at the next space after the numeric value that would be great.

In the last example there is a full stop so this is bringing in £87k., reason I mention this is that the next space is after the . but I can always do a find and replace with this.

Text	Result	Expected Result
The board approves £657k though no more till the next project milestone has been attained	£657k t	£657k
This time around funds that are made available are £7.6m but shall keep an eye on all funds.	£7.6m b	£7.6m
We agree to the drawdown of 80% of funds which are £676k	£676k	£676k
there is to be no movement on the release of funds from, current spend stands at £87k. This shall be reviewed again next gathering	£87k.	£87k

Help is appreciated as always

 

[RoryA]

You could use:

=MID(A1,FIND("£",A1),FIND(" ",A1&" ",FIND("£",A1))-FIND("£",A1))

 

[Fluff]

How about

Fluff.xlsm
	A	B
1
2	The board approves £657k though no more till the next project milestone has been attained	£657k
3	This time around funds that are made available are £7.6m but shall keep an eye on all funds.	£7.6m
4	We agree to the drawdown of 80% of funds which are £676k	£676k
5	there is to be no movement on the release of funds from, current spend stands at £87k. This shall be reviewed again next gathering	£87k.
Master

Cell Formulas
Range	Formula
B2:B5	B2	=FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m[contains(.,'£')]")

 

[Arts]

How about

Fluff.xlsm
	A	B
1
2	The board approves £657k though no more till the next project milestone has been attained	£657k
3	This time around funds that are made available are £7.6m but shall keep an eye on all funds.	£7.6m
4	We agree to the drawdown of 80% of funds which are £676k	£676k
5	there is to be no movement on the release of funds from, current spend stands at £87k. This shall be reviewed again next gathering	£87k.
Master

Cell Formulas
Range	Formula
B2:B5	B2	=FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m[contains(.,'£')]")

Amazing exactly what I was after! Thank you !

 

[Arts]

You could use:

=MID(A1,FIND("£",A1),FIND(" ",A1&" ",FIND("£",A1))-FIND("£",A1))

Thanks Rory! I was actually going to ask if there was a non 365 way using MID, FIND, LEFT etc for learning purposes.

Ps I would mark both as a solution but am only allowed to choose one, for some reason I didn't see your response till after...

 

[Fluff]

The formula I suggested is a non 365 way, as your profile shows you are using 2016.

 

[Arts]

The formula I suggested is a non 365 way, as your profile shows you are using 2016.

Oh apologies I thought FILTERXML was part of the new school! Thanks for letting me know! Showing my age here anything not using LEFT, RIGHT, MID I see as a new function