break the number in the brackets

[kin]

Hello I have a column where there is data like this - (5-40-528) and would like to make the numbers in it 5-40-528 to appear in a different column

 

[Rick Rothstein]

Wow, am I glad I decided to open the file you gave the link for... your original description was totally misleading given the file's structure. If your "numbers in parentheses" are always one digit followed by a dash followed by two digits followed by a dash followed by three digits AND assuming it is always the last part of your text string, then try this formula...

=MID(A2,LEN(A2)-8,8)

If your numbers are not always the number of digits shown, then (still assuming it is always the last part of your text string) try this formula instead...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A2,LEN(A2)-1),"(",REPT(" ",99)),99))

 

[Sektor]

Rick Rothstein To avoid "ifs", more safer to use regex:

[COLOR="Blue"]Function[/COLOR] Separate(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "(\d+)-(\d+)-(\d+)"
        Separate = .Execute(Str)(0)
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[Peter_SSs]

I haven't looked at the linked file but it sounds like we are trying to extract from parentheses at the end of a string. If so, then this may be a worthwhile vba approach, rather than attacking it row by row.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Extraction()<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> SourceCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A" <SPAN style="color:#007F00">'<-- Change to suit</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> DestCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "J"   <SPAN style="color:#007F00">'<-- Change to suit</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    LR = Cells(Rows.Count, SourceCol).End(xlUp).Row<br>    <SPAN style="color:#00007F">With</SPAN> Cells(1, DestCol).Resize(LR)<br>        .Value = Cells(1, SourceCol).Resize(LR).Value<br>        .Replace What:="*(", Replacement:="", LookAt:=xlPart, _<br>            SearchFormat:=False, ReplaceFormat:=False<br>        .Replace What:=")", Replacement:="", LookAt:=xlPart, _<br>            SearchFormat:=False, ReplaceFormat:=False<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Rick Rothstein]

Rick Rothstein To avoid "ifs", more safer to use regex:

[COLOR=blue]Function[/COLOR] Separate(Str [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]) [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]
    [COLOR=blue]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "(\d+)-(\d+)-(\d+)"
        Separate = .Execute(Str)(0)
    [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Function[/COLOR]

Of course, we can do this without resorting to RegEx...

Function Separate(Strng As String) As String
  Dim X As Long, OpenParen As Long, CloseParen As Long, Txt As String
  OpenParen = InStr(Strng, "(")
  Do While OpenParen
    CloseParen = InStr(OpenParen, Strng, ")")
    Txt = Mid(Strng, OpenParen + 1, CloseParen - OpenParen - 1)
    If Not Txt Like "*[!0-9-]*" Then
      If Txt Like "*-*-*" Then
        Separate = Txt
        Exit Function
      End If
    End If
    OpenParen = InStr(OpenParen + 1, Strng, "(")
  Loop
End Function

 

[Sektor]

I tried on your code with "( 5-40-528 )" and it failed.

 

[Peter_SSs]

I tried on your code with "( 5-40-528 )" and it failed.

But then the current RegExp function also fails with the following two examples.
In both cases it returns text not enclosed in parentheses.

abc 5-40-528
abc 5-40-529 def (4-78-654)

Of course this may not be possible with the OP's data, just as "( 5-40-528 )" may not be possible.

 

[Sektor]

Correction for Peter:

[COLOR="Blue"]Function[/COLOR] Separate(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] mc [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Object[/COLOR]
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "\(\s*((\d+)-(\d+)-(\d+))\s*\)"
        [COLOR="Blue"]Set[/COLOR] mc = .Execute(Str)
        [COLOR="Blue"]If[/COLOR] mc.Count > 0 [COLOR="Blue"]Then[/COLOR]
            Separate = mc(0).SubMatches(0)
        [COLOR="Blue"]Else[/COLOR]
            Separate = ""
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[Rick Rothstein]

I tried on your code with "( 5-40-528 )" and it failed.

I presume you meant my code. No problem as the fix is easy (just needed to add a Trim function call)...

Function Separate(Strng As String) As String
  Dim X As Long, OpenParen As Long, CloseParen As Long, Txt As String
  OpenParen = InStr(Strng, "(")
  Do While OpenParen
    CloseParen = InStr(OpenParen, Strng, ")")
    Txt = Trim(Mid(Strng, OpenParen + 1, CloseParen - OpenParen - 1))
    If Not Txt Like "*[!0-9-]*" Then
      If Txt Like "*-*-*" Then
        Separate = Txt
        Exit Function
      End If
    End If
    OpenParen = InStr(OpenParen + 1, Strng, "(")
  Loop
End Function

 

[kin]

I haven't looked at the linked file but it sounds like we are trying to extract from parentheses at the end of a string. If so, then this may be a worthwhile vba approach, rather than attacking it row by row.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Extraction()<br>****<SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>****<br>****<SPAN style="color:#00007F">Const</SPAN> SourceCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A" <SPAN style="color:#007F00">'<-- Change to suit</SPAN><br>****<SPAN style="color:#00007F">Const</SPAN> DestCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "J"** <SPAN style="color:#007F00">'<-- Change to suit</SPAN><br>****<br>****Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>****LR = Cells(Rows.Count, SourceCol).End(xlUp).Row<br>****<SPAN style="color:#00007F">With</SPAN> Cells(1, DestCol).Resize(LR)<br>********.Value = Cells(1, SourceCol).Resize(LR).Value<br>********.Replace What:="*(", Replacement:="", LookAt:=xlPart, _<br>************SearchFormat:=False, ReplaceFormat:=False<br>********.Replace What:=")", Replacement:="", LookAt:=xlPart, _<br>************SearchFormat:=False, ReplaceFormat:=False<br>****<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>****Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

it worked instantly. Thanks and thank you all for the support