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.

    Please help.


  • Re: Text Formula

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

    =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 :)

  • Re: Text Formula


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

    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.


Participate now!

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