Text Formula

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

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

This too with your original - you were close

=MID(A1,SEARCH("/",A1)+2,SEARCH("]",A1) -SEARCH("/",A1)-3)

Smallman

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..

Private Sub CommandButton1_Click()
Dim r As Range
Dim m As Object
Dim L As Long
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Pattern = "\/(.*?)\/(.*?)\]"
        For Each r In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
        If .test(r.Value) Then
                Set m = .Execute(r.Value)
                L = Len(m(i)) - 1
                    r(, 2).Value = Trim(Right(Left(m(i), L), L - 1))
            End If
        Next r
    End With
End Sub