How to extract words in between parenthesis and create a new tab with that name

[Pookiemeister]

I am wanting to extract value in between parenthesis and create a new tab and rename it with the extracted value? How can I begin? The parenthesis are located in column "B". So I know I would need to loop through each range in that field. Thanks

 

[Peter_SSs]

Could we have a small set of sample data with XL2BB and explain in relation to that?

It would help clarify if it is possible more than one set of parentheses can occur in the one cell, exactly what you mean by "each range in that field" etc.

 

[jolivanes]

Only works if, like Peter alluded to, you have one opening and one closing bracket in the string.

Sub Like_So()
Dim c As Range, aws As Worksheet
Set aws = ActiveSheet
Application.ScreenUpdating = False
    For Each c In Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
        If InStr(c, "(") <> 0 And InStr(c, ")") <> 0 Then
            ActiveWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
            ActiveSheet.Name = Split(Split([c], "(")(1), ")")(0)
        End If
    Next c
aws.Select
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Only works if, ..

There may also be considerations like

• Could a sheet with that name already exist?
• Could the text include characters that are not allowed in sheet names?
• Could the text be longer than is allowed for a sheet name?

If any of the above could happen what action should result?

 

[Pookiemeister]

@Peter_SSs and @jolivanes My apologies for not including any data. I wanted to post this before I left for work, and I forgot to include some form of data. To answer Peters questions:

• Could a sheet with that name already exist? NO (Brand new Workbook created, only one worksheet displayed)
• Could the text include characters that are not allowed in sheet names? NO
• Could the text be longer than is allowed for a sheet name? NO

Here is an example of the text:
4K01 SCL 300 (CHA0400-PAC001-BAT0300)

So, I would like a worksheet named or even better created with the following as its name. In this example. the worksheet would be named: CHA0400-PAC001-BAT0300. Thanks

 

[Pookiemeister]

I just tried @jolivanes code and it worked. This is exactly what I needed. Thank you.

 

[Peter_SSs]

I just tried @jolivanes code and it worked. This is exactly what I needed.

Good news.
If you are interested there are a couple of shortenings you could use.
- A simpler test to check for the parentheses
- You can add, place and name the new worksheet all at once.

Sub Like_So_v2()
  Dim c As Range, aws As Worksheet
  Set aws = ActiveSheet
  Application.ScreenUpdating = False
  For Each c In Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
    If c Like "*(*)*" Then Sheets.Add(After:=Sheets(Sheets.Count)).Name = Split(Split([c], "(")(1), ")")(0)
  Next c
  aws.Activate
  Application.ScreenUpdating = True
End Sub

 

[jolivanes]

@Pookiemeister
Thanks for the update.

@Peter_SSs
Thanks for the more efficient version