Extracting all text after and including a percentage value that varies in length

[MichelleP]

Hi I have a text string in excel such as

there were 120 visitors to the park today 25% of whom were children. I want to excluded everything from 25% from the statement. the only problem is that 25% could be 25.5% or 25.998% etc. there is also no commonality in the word prior to the % value.

I have tried =LEFT(B2;FIND("0.0##%";B2)) but it does not recognise the %.

Any help would be greatly appreciated

Thanks

[TMS]

Try:

Please Login or Register  to view this content.

Regards, TMS

[MichelleP]

Hi thanks for that but that displays everything to the right o 25%. I want everything to the left of 25% eg there were 120 visitors to the park today.

[protonLeah]

Does,
"...excluded everything from 25%..." mean you want just the 25%
or: "...25% of whom were children"

[MichelleP]

Hi what show in the next cell "there were 120 visitors to the park today" and excluded everything else.

[AlKey]

Like this?

Please Login or Register  to view this content.

	A	B
1	there were 120 visitors to the park today 25% of whom were children	there were 120 visitors to the park today

Sheet: Sheet2

[TMS]

Your thread title says:

Extracting all text after

Please Login or Register  to view this content.

[MichelleP]

Hi

Thanks for your reply. The TRIM formula =TRIM(LEFT(A2,FIND("%",A2)+1)) is still including the 25% in the result and I need to exclude this. eg I need the output to just say "there were 120 visitors to the park today"

The other formula =LEFT(A1,LOOKUP(10^308,FIND(" ",LEFT(A1,FIND(" ",A1&" ",FIND("%",A1))-1),ROW(INDIRECT("1:"&LEN($A1)))))) is providing the correct answer so thank you for that.