# Text Formula

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• Good morning All,

I am trying to create a formula that will extract the words "Mindray BS 380/ BS 400" from the text "[IFCC/ Mindray BS 380/ BS 400]". I am very close, but can't quite finish it. I have so far used the formula =MID(A1,SEARCH("/",A1)+2,SEARCH("]",A1)), which has given me the result Mindray BS 380/ BS 400]. I can't seem to get rid of that outer square bracket.

It has to be a dynamic formula as the text within the square brackets will not always be the same.

Herbz

• Re: Text Formula

Hope this is what you're after. With A1 = [IFCC/ Mindray BS 380/ BS 400], paste the following in B1:

Code
``=TRIM(LEFT(SUBSTITUTE(MID(\$A1,FIND("|",SUBSTITUTE(\$A1,"[","|",1))+1,LEN(\$A1)),"]",REPT(" ",LEN(\$A1))),LEN(\$A1)))``

This formula returns the characters between the 2 brackets ([XXX]) regardless of length and can be dragged all the way down column B.

I've attached an example for you too

## Files

• Re: Text Formula

Quote from Herbz

"Mindray BS 380/ BS 400" from the text "[IFCC/ Mindray BS 380/ BS 400]".

Perhpas

=TRIM(MID(A1,FIND("/",A1)+2,SUM(FIND({"]","/"},A1)*{1,-1})-2))

• Re: Text Formula

Hi..

This seems to do it..
There is probably a better pattern.. but it seems to work..

Edit:
Err.. just noticed.. you specifically want a formula.. my bad..

• Re: Text Formula

Wow, guys this is awesome, many thanks for all the varied approaches, really appreciate. I have just learnt some funky new approaches.

Herbz

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!