add to character to string in query

Z

zezo2021

Member
Local time
Today, 16:08
Joined
Mar 25, 2021
Messages
412
friends
if I have a column containing a string like this
CLE Credit (50min)(29601),CLE Credit (60min)(29601),Attendance(:_____)

I want to create an expression column to check if (2961) is a number only add bb to this number

for example, the string above will be

CLE Credit (50min)(BB29601),CLE Credit (60min)(BB29601),Attendance(:_____)


IS THIS POSSIBLE


THANK YOU IN ADVANCE
 
was going to answer, but will leave for Arnel
 
Have you tried Replace()?
 
Can this code help


Code: 
this code may help
Public Function fnGetState_ID(ByVal strText) As Variant
    ' the function will return a
    ' 1 dimension array in State, ID Pair.
    Dim arr() As String
    Dim allMatches As Variant, result As Variant
    Dim i As Integer, j As Integer, k As Integer
    ' create a big array
    ReDim arr(1000)
    fnGetState_ID = Null
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = "([A-Z\ ]{2,3})([0-9]{4,10})"
        Set allMatches = .Execute(strText)
        i = -1
        For j = 0 To allMatches.Count - 1
            For k = 0 To allMatches.Item(j).submatches.Count - 1
                i = i + 1
                arr(i) = Trim$(allMatches.Item(j).submatches.Item(k))
            Next
        Next
    End With
    If i > -1 Then
        ReDim Preserve arr(i)
        fnGetState_ID = arr
    End If
End Function
 
zezo2021 said:
Can this code help


Code: 
this code may help
Public Function fnGetState_ID(ByVal strText) As Variant
    ' the function will return a
    ' 1 dimension array in State, ID Pair.
    Dim arr() As String
    Dim allMatches As Variant, result As Variant
    Dim i As Integer, j As Integer, k As Integer
    ' create a big array
    ReDim arr(1000)
    fnGetState_ID = Null
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = "([A-Z\ ]{2,3})([0-9]{4,10})"
        Set allMatches = .Execute(strText)
        i = -1
        For j = 0 To allMatches.Count - 1
            For k = 0 To allMatches.Item(j).submatches.Count - 1
                i = i + 1
                arr(i) = Trim$(allMatches.Item(j).submatches.Item(k))
            Next
        Next
    End With
    If i > -1 Then
        ReDim Preserve arr(i)
        fnGetState_ID = arr
    End If
End Function
Click to expand...
It can help depending on your needs. Without really knowing what that is, we can't say for sure if that code will cover it.
 
zezo2021 said:
friends
if I have a column containing a string like this
CLE Credit (50min)(29601),CLE Credit (60min)(29601),Attendance:)_____)

I want to create an expression column to check if (2961) is a number only add bb to this number

for example, the string above will be

CLE Credit (50min)(BB29601),CLE Credit (60min)(BB29601),Attendance:)_____)


IS THIS POSSIBLE


THANK YOU IN ADVANCE
Click to expand...
 
here test this:
Code: 
Public Function fnGetState_ID(ByVal strText) As Variant
    ' the function will return a
    ' 1 dimension array in State, ID Pair.
    Dim arr() As String, s As String
    Dim allMatches As Variant, result As Variant
    Dim i As Integer, j As Integer, k As Integer
    ' create a big array
    ReDim arr(1000)
    fnGetState_ID = Null
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = "([A-Z\ ]{2,3})([0-9]{4,10})|(\([0-9]{4,10}\))"
        Set allMatches = .Execute(strText)
        i = -1
        For j = 0 To allMatches.Count - 1
            For k = 0 To allMatches.Item(j).submatches.Count - 1
                s = Trim$(allMatches.Item(j).submatches.Item(k))
                If Len(s) <> 0 Then
                    i = i + 1
                    If s = "(29601)" Then
                        arr(i) = "BB"
                        i = i + 1
                        arr(i) = "29601"
                    Else
                        arr(i) = s
                    End If
                End If
            Next
        Next
    End With
    If i > -1 Then
        ReDim Preserve arr(i)
        fnGetState_ID = arr
    End If
End Function
 
arnelgp said:
here test this:
Code: 
Public Function fnGetState_ID(ByVal strText) As Variant
    ' the function will return a
    ' 1 dimension array in State, ID Pair.
    Dim arr() As String, s As String
    Dim allMatches As Variant, result As Variant
    Dim i As Integer, j As Integer, k As Integer
    ' create a big array
    ReDim arr(1000)
    fnGetState_ID = Null
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = "([A-Z\ ]{2,3})([0-9]{4,10})|(\([0-9]{4,10}\))"
        Set allMatches = .Execute(strText)
        i = -1
        For j = 0 To allMatches.Count - 1
            For k = 0 To allMatches.Item(j).submatches.Count - 1
                s = Trim$(allMatches.Item(j).submatches.Item(k))
                If Len(s) <> 0 Then
                    i = i + 1
                    If s = "(29601)" Then
                        arr(i) = "BB"
                        i = i + 1
                        arr(i) = "29601"
                    Else
                        arr(i) = s
                    End If
                End If
            Next
        Next
    End With
    If i > -1 Then
        ReDim Preserve arr(i)
        fnGetState_ID = arr
    End If
End Function
Click to expand...
Hello

Can this code work with the entire column not specific Value 29601


I need it for column strings not individual string


Thank you
 
Code: 
Public Function fnGetState_ID(ByVal strText) As Variant
    ' the function will return a
    ' 1 dimension array in State, ID Pair.
    Dim arr() As String, s As String
    Dim allMatches As Variant, result As Variant
    Dim i As Integer, j As Integer, k As Integer
    ' create a big array
    ReDim arr(1000)
    fnGetState_ID = Null
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = "([A-Z\ ]{2,3})([0-9]{4,10})|(\([0-9]{4,10}\))"
        Set allMatches = .Execute(strText)
        i = -1
        For j = 0 To allMatches.Count - 1
            For k = 0 To allMatches.Item(j).submatches.Count - 1
                s = Trim$(allMatches.Item(j).submatches.Item(k))
                If Len(s) <> 0 Then
                    i = i + 1
                    If s Like "(*)" Then
                        arr(i) = "BB"
                        i = i + 1
                        arr(i) = Replace$(Replace$(s, "(", ""), ")", "")
                    Else
                        arr(i) = s
                    End If
                End If
            Next
        Next
    End With
    If i > -1 Then
        ReDim Preserve arr(i)
        fnGetState_ID = arr
    End If
End Function
 
arnelgp said:
Code: 
Public Function fnGetState_ID(ByVal strText) As Variant
    ' the function will return a
    ' 1 dimension array in State, ID Pair.
    Dim arr() As String, s As String
    Dim allMatches As Variant, result As Variant
    Dim i As Integer, j As Integer, k As Integer
    ' create a big array
    ReDim arr(1000)
    fnGetState_ID = Null
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = "([A-Z\ ]{2,3})([0-9]{4,10})|(\([0-9]{4,10}\))"
        Set allMatches = .Execute(strText)
        i = -1
        For j = 0 To allMatches.Count - 1
            For k = 0 To allMatches.Item(j).submatches.Count - 1
                s = Trim$(allMatches.Item(j).submatches.Item(k))
                If Len(s) <> 0 Then
                    i = i + 1
                    If s Like "(*)" Then
                        arr(i) = "BB"
                        i = i + 1
                        arr(i) = Replace$(Replace$(s, "(", ""), ")", "")
                    Else
                        arr(i) = s
                    End If
                End If
            Next
        Next
    End With
    If i > -1 Then
        ReDim Preserve arr(i)
        fnGetState_ID = arr
    End If
End Function
Click to expand...
testing
 
Whilst I'm sure @arnelgp will work it out, us normal mortals really need to see your starting data and what you expect to see out as a result, more than one example to see a pattern.
 
arnelgp said:
Code: 
Public Function fnGetState_ID(ByVal strText) As Variant
    ' the function will return a
    ' 1 dimension array in State, ID Pair.
    Dim arr() As String, s As String
    Dim allMatches As Variant, result As Variant
    Dim i As Integer, j As Integer, k As Integer
    ' create a big array
    ReDim arr(1000)
    fnGetState_ID = Null
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = "([A-Z\ ]{2,3})([0-9]{4,10})|(\([0-9]{4,10}\))"
        Set allMatches = .Execute(strText)
        i = -1
        For j = 0 To allMatches.Count - 1
            For k = 0 To allMatches.Item(j).submatches.Count - 1
                s = Trim$(allMatches.Item(j).submatches.Item(k))
                If Len(s) <> 0 Then
                    i = i + 1
                    If s Like "(*)" Then
                        arr(i) = "BB"
                        i = i + 1
                        arr(i) = Replace$(Replace$(s, "(", ""), ")", "")
                    Else
                        arr(i) = s
                    End If
                End If
            Next
        Next
    End With
    If i > -1 Then
        ReDim Preserve arr(i)
        fnGetState_ID = arr
    End If
End Function
Click to expand...
Done
(y) (y) (y) (y)
 
@arnelgp


can you explain this line of code
.Pattern = "([A-Z\ ]{2,3})([0-9]{4,10})|(\([0-9]{4,10}\))"
 
zezo2021 said:
@arnelgp


can you explain this line of code
.Pattern = "([A-Z\ ]{2,3})([0-9]{4,10})|(\([0-9]{4,10}\))"
Click to expand...
Hi. You can also plug your expression on this website and get an explanation. Just a thought...

regex101.com

regex101: build, test, and debug regex

Regular expression tester with syntax highlighting, explanation, cheat sheet for PHP/PCRE, Python, GO, JavaScript, Java, C#/.NET, Rust.
regex101.com regex101.com
 
theDBguy said:
Hi. You can also plug your expression on this website and get an explanation. Just a thought...

regex101.com

regex101: build, test, and debug regex

Regular expression tester with syntax highlighting, explanation, cheat sheet for PHP/PCRE, Python, GO, JavaScript, Java, C#/.NET, Rust.
regex101.com regex101.com
Click to expand...
goo site but didn't get satisfactory information



What about the space

CLE Credit (50min)(MT 29601),CLE Credit (60min)(MT 29601),Attendance:)_____)

Is there problem if there is a space between text and number?

@arnelgp

can you comment the code so I can understand and edit it later?

Thank you
 
You must log in or register to reply here.

Similar threads

D
Solved Nesting IIF Statement with functions that end in (field)
Replies
13
Views
319
arnelgp
arnelgp
J
Creating a Running Balance Query
2 3
Replies
54
Views
1,536
AngelSpeaks
A
E
Dlookup on unbound text
Replies
10
Views
294
GPGeorge
GPGeorge
C
Help with my Keypad
Replies
5
Views
274
ChrisLeicester
C
D
Solved Form jumping around in records when i field is typed
2
Replies
31
Views
632
mike60smart
mike60smart

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom