Extract each substring from current cell

**clickcoder** · 02-15-2010, 10:53 AM

Hi friends,

A1 cell data is : Microsoft (MS) Google (G) Yahoo (Yho)

I want to extract each words who start with ( and end with ).

B1 will be (MS)
C1 will be (G)
D1 will be (Yho)

How can I do that?

Thank you

**6StringJazzer** · 02-15-2010, 12:48 PM

Here are two solutions. The first requires "helper" cells. It's more efficient but if you don't care for that you can construct "megaformulas" as shown in the second solution.

**clickcoder** · 02-16-2010, 03:22 AM

Originally Posted by 6StringJazzer

Here are two solutions. The first requires "helper" cells. It's more efficient but if you don't care for that you can construct "megaformulas" as shown in the second solution.

Dear 6StringJazzer,

Complex one is good for me

But when I look your example it couldn't found 3rd (Yho)

Thanks for all friends for their suggestions

**mdbct** · 02-15-2010, 01:07 PM

Try this one:

="(" & MID(SUBSTITUTE($A1,"(","|",COLUMN()-1),FIND("|",SUBSTITUTE($A1,"(","|",COLUMN()-1))+1,FIND(")",SUBSTITUTE($A1,"(","|",COLUMN()-1),FIND("|",SUBSTITUTE($A1,"(","|",COLUMN()-1)))-FIND("|",SUBSTITUTE($A1,"(","|",COLUMN()-1)))

**DonkeyOte** · 02-15-2010, 03:59 PM

Another single cell alternative perhaps:

B1:
="("&TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"(",REPT(" ",255),COLUMNS($B1:B1)),")",REPT(" ",255),COLUMNS($B1:B1)),255,255))&")"
copied across to D1

**clickcoder** · 02-16-2010, 04:51 AM

For last word I used
=RIGHT(A2,LEN(A2)-FIND("^^",SUBSTITUTE(A2,"(","^^",LEN(A2)-LEN(SUBSTITUTE(A2,"(",""))))+1)

But still don't like this solution because if I have more than 4 parantheses it will crash

**6StringJazzer** · 02-16-2010, 10:48 AM

I had an error, which is corrected in the attached but the formula becomes very unwieldy compared to the ones above.

I have also added a UDF here if you care to use one, also in the attached.

' Extract the nth occurrence of text delimited by ldelim and rdelim, including the
' delimiters
Public Function DelimitedText( _
                  c As Range, _
                  ldelim As String, _
                  rdelim As String, _
                  Optional n As Long = 1) _
                As String

   Dim s As Variant
   Dim stemp As String
   Dim result As Variant
   
   result = InStr(c.Value, ldelim)
   If result = 0 Then
      DelimitedText = "#LDELIM!" ' left delimited not found
   Else
   
      s = Split(c.Value, ldelim)
      stemp = ldelim & s(n)
      result = InStr(stemp, rdelim)
      If result = 0 Then
         DelimitedText = "#RDELIM!" ' right delimiter not found
      Else
         DelimitedText = Mid(stemp, 1, InStr(stemp, rdelim))
      End If
   End If

End Function