extracting string from cell

[Donjt81]

I have a document with data in column A (about 5000 rows).

They look like this

This is a great day (1234)
whats for dinner (23322)
What is your name (0989)

I want to extract the number in the brackets. I would have used text to columns with '(' and then ')' as the delimiter. But the problem is that the data is not very clean. There are records like this

I (am messing) up your parse (345352)
How (do) you feel about this (425112)

so a simple text to columns won't work. One thing is for sure about the data in column A, it always ends with the (324245) number. How can I extract just that number out. FYI number length is not always the same. sometimes its 6 digits, sometimes 7 digits, etc.

 

[Rick Rothstein]

This formula should do what you asked for...

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

Note that it returns your number as Text... this will preserve leading zeroes; however, if you don't care about leading numbers and want a real number returned, then use this formula...

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

 

[Marcelo Branco]

Hi,

Try this UDF

Public Function getNums(t As String)
    Dim RegEx As Object, RegMatch As Object
 
    Set RegEx = CreateObject("VBScript.RegExp")
 
    With RegEx
        .MultiLine = False
        .Global = False
        .IgnoreCase = False
        .Pattern = "(\(\d+\))$"
        Set RegMatch = .Execute(t)
    End With
 
    getNums = Replace(Replace(RegMatch(0), "(", ""), ")", "")
End Function

Copy the code above (Ctrl+C)
Alt-F11 to open the VBEditor
Insert > Module
Paste (Ctrl+V) in the right-panel

Usage
A B
<TABLE style="WIDTH: 268pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=357><COLGROUP><COL style="WIDTH: 205pt; mso-width-source: userset; mso-width-alt: 9984" width=273><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 205pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=273>I (am messing) up your parse (345352)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=84>345352</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>How (do) you feel about this (425112)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>425112</TD></TR></TBODY></TABLE>

Formula in B1
=getNums(A1)

copy down

HTH

M.

 

[Rick Rothstein]

Try this UDF

If the OP really wants a UDF, then here is a shorter one for him to consider which does not require the use of Regular Expressions...

Function GetNumber(S As String) As String
  Dim Temp() As String
  Temp = Split(Left(S, Len(S) - 1), "(")
  GetNumber = Temp(UBound(Temp))
End Sub

EDIT
Just fooling around here now, but knowing there are people out there who like to see such things, we can make this UDF into a one-liner (I wouldn't in actual use, though, as it is less efficient than the first UDF I posted)...

Function GetNumber(S As String) As String
  GetNumber = Split(Left(S, Len(S) - 1), "(")(UBound(Split(Left(S, Len(S) - 1), "(")))
End Function

 

[Scott Huish]

To return a number in UDF:

Function NumberOnly(r As String) As Long
NumberOnly = -Mid(r, InStrRev(r, "("))
End Function

 

[Rick Rothstein]

To return a number in UDF:

Function NumberOnly(r As String) As Long
NumberOnly = -Mid(r, InStrRev(r, "("))
End Function

Good point!

 

[Marcelo Branco]

If the OP really wants a UDF, then here is a shorter one for him to consider which does not require the use of Regular Expressions...

Rick,

My intention was only to propose an alternative to a formula (in fact, i hadnt seen your #2 when i sent my post) and to ilustrate the use of Regular expressions - sometimes can be a good solution...
( i think that is the spirit of this forum. If i'm wrong, sorry, apologize!)

I learned from you and HOTPEPPER that its possible to write shorter and more efficient functions. Great!

Regards,

M.

 

[mikerickson]

Function NumberInParenthesis(aString) as Double
    On Error Resume Next
    NumberInParenthesis = Val(Split(aString, "(")(1))
    On Error Goto 0
Exit Function

 

[Rick Rothstein]

Rick,

My intention was only to propose an alternative to a formula

I'm sorry, but my wording may have misled you as to my intentions... I was not criticizing your posting at all... I had assumed the OP wanted a formula solution, but when you offered your UDF, I just wanted to show that a RegEx solution was not the only way to do it. As it turns out, HOTPEPPER offered a far better method for avoiding the use of RegEx than I did.

 

[Rick Rothstein]

Function NumberInParenthesis(aString) as Double
    On Error Resume Next
    NumberInParenthesis = Val(Split(aString, "(")(1))
    On Error Goto 0
Exit Function

That finds the first item in parentheses, not the last one, right? The OP indicated he wanted the last one. Just to note, though, that if he had wanted the first one, you could have avoided the need for the error trap in your function by supplying an opening parentheses for Split to find in case the text string didn't have one...

Function NumberInParenthesis(aString) As Double
    NumberInParenthesis = Val(Split(aString & "(", "(")(1))
End Function