Data in bracket from another column (1 Viewer)

Dissander

Registered User.
Local time
Today, 00:59
Joined
Mar 22, 2017
Messages
29
Dear all,
Could you help me please?
I have a column of data in the following format: ALBUMIN (35). I would like to have a query where a separate column would show only the numbers in Bracket. I would really appreciate your help & support on this.
Thank you.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:59
Joined
Feb 19, 2002
Messages
42,981
If the format is consistent, then:
Select ..., Mid(YourColumn, Instr(YourColumn,"(") +1) as NumPart, fld2, fld3, .....

You use the Instr() function to find the first occurrence of the left parenthesis and the Mid() function to use that location as the starting point to pull out the rest of the string.

Of course it is FAR BETTER to never mush data to begin with.
 

Dissander

Registered User.
Local time
Today, 00:59
Joined
Mar 22, 2017
Messages
29
Thank you Pat. I'll give it a try and get back to you.
Thank you so much.
I really appreciate it.
 

isladogs

MVP / VIP
Local time
Today, 07:59
Joined
Jan 14, 2017
Messages
18,186
Pat's code will remove everything up to the ( bracket to give you 35 followed by )

The following will also remove the )
so AlbumIn(35) => 35
TVOut(113A) => 113A
ABC(230)DEF => 230 etc

Code:
Mid(YourColumn, Instr(YourColumn,"(")+1,InStr(YourColumn,")")-InStr(YourColumn,"(")-1)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:59
Joined
Feb 19, 2002
Messages
42,981
Thanks Colin. I got distracted and forgot to finish the expression.
 

Dissander

Registered User.
Local time
Today, 00:59
Joined
Mar 22, 2017
Messages
29
Thank you all. Worked like a charm. Exactly what I needed. Thank you.
 

sxschech

Registered User.
Local time
Today, 00:59
Joined
Mar 2, 2010
Messages
791
Here are functions that can extract numbers or text. However, it may not do what is intended if there are numbers elsewhere as they would be consolidated like example 2 below. So as long as the only numbers are the ones in parens, it should work. --It does not mean the numbers have to be in parens, just that if there are numbers elsewhere in the string that aren't part of what you need, they would be included--

ABC(230)DEF => 230
fextractnumeric("ABC(230)DEF")
---------------------------------------
230

fextractnumeric("ABC(230)DEF 35")
--------------------------------
23035

fextractnumeric("This is a set of 5 numbers")
-------------------------------------------------------
5


fextractnumeric(yourfieldname)

Code:
    Public Function fExtractNumeric(strInput) As String
        'http://bytes.com/topic/access/answers/862765-extracting-number-string
        ' Returns the numeric characters within a string in
        ' sequence in which they are found within the string
        '20150506
        Dim strResult As String, strCh As String
        Dim intI As Integer
        If Not IsNull(strInput) Then
            For intI = 1 To Len(strInput)
                strCh = Mid(strInput, intI, 1)
                Select Case strCh
                    Case "0" To "9"
                        strResult = strResult & strCh
                    Case Else
                End Select
            Next intI
        End If
        fExtractNumeric = strResult
    End Function
Code:
Public Function fExtractString(strInput) As String
        'http://bytes.com/topic/access/answers/862765-extracting-number-string
        ' Returns the alphabetic characters within a string in
        ' sequence in which they are found within the string
        '20150506
        Dim strResult As String, strCh As String
        Dim intI As Integer
        If Not IsNull(strInput) Then
            For intI = 1 To Len(strInput)
                strCh = Mid(strInput, intI, 1)
                Select Case strCh
                    Case "0" To "9"
                    Case Else
                        strResult = strResult & strCh
                End Select
            Next intI
        End If
        fExtractString = strResult
    End Function
 
Last edited:

sxschech

Registered User.
Local time
Today, 00:59
Joined
Mar 2, 2010
Messages
791
Found a function that will extract a string between two strings. Solves the issue mentioned in post with function fextractnumeric. Looks like this one can handle * as the character rather than treating as wildcard.

? getbetween("ABC(230)DEF 35","(",")")
230

? getbetween("This has (data 123) in here","(",")")
data 123

? getbetween("We should pull out data between *Whatever is inside the stars* and toss all else","*","*")
Whatever is inside the stars

? getbetween("Tracking Code:54556, 4325, 221A / CustomerCode:XYZX",":","/")
54556, 4325, 221A

? getbetween("Tracking Code:54556, 4325, 221A / CustomerCode:XYZX /","CustomerCode:","/")
XYZX

From
http://www.vitoshacademy.com/vba-excel-string-between-two-strings/

Code:
Public Function GetBetween(ByRef sSearch As String, ByRef sStart As String, ByRef sStop As String, _
                           Optional ByRef lSearch As Long = 1, Optional ByRef hasParagraph As Boolean, Optional ByRef HasParagraphBefore As Boolean) As String
    lSearch = InStr(lSearch, sSearch, sStart)
    If lSearch > 0 Then
        lSearch = lSearch + Len(sStart)
        Dim lTemp As Long
        lTemp = InStr(lSearch, sSearch, sStop)
 
        If lTemp > lSearch Then
            If hasParagraph Then
                GetBetween = Mid$(sSearch, lSearch, lTemp - lSearch - 2)
            Else
                GetBetween = Mid$(sSearch, lSearch, lTemp - lSearch)
            End If
 
            If HasParagraphBefore Then
                GetBetween = Right(GetBetween, Len(GetBetween) - 2)
            End If
        End If
    End If
 
End Function
Code:
Public Function getLast(ByRef sTextForEdit As String, ByRef sTempText As String) As String
    Dim lPosition As Long
 
    lPosition = InStr(sTextForEdit, sTempText)
    getLast = Right(Mid(sTextForEdit, lPosition), Len(Mid(sTextForEdit, lPosition)) - Len(sTempText))
 
End Function
 

Users who are viewing this thread

Top Bottom