get text form string column

Z

zezo2021

Member
Local time
Today, 17:49
Joined
Mar 25, 2021
Messages
412
How can I extract text from Column?

Ct (60min)(PA208460),CT (50min)(PA208460),Attendance(Yes)
Ct (60min)(PA208460),CT (50min)(PA208460),Attendance(Yes)
Ct (60min)(WA 208460),CT (50min)(WA 208460),Attendance(Yes)
Ct (60min)(WA 208460),CT (50min)(WA 208460),Attendance(Yes)
Ct (60min)(TX 208460),CT (50min)(TX 208460),Attendance(Yes)

I want these columns after extract
========================================================
String column ,column Heading PA , column HeadingTX ,column HeadingWA
========================================================
Ct (60min)(PA208460),CT (50min)(PA208460),Attendance(Yes),2,0,0
Ct (60min)(PA208460),CT (50min)(PA208460),Attendance(Yes),2,0,0
Ct (60min)(WA 208460),CT (50min)(WA 208460),Attendance(Yes),0,0,2
Ct (60min)(WA 208460),CT (50min)(WA 208460),Attendance(Yes),0,0,2
Ct (60min)(TX 208460),CT (50min)(TX 208460),Attendance(Yes),0,2,0
 
Hi. Try using the code tags to preserve your column formatting, so we can better understand your column definitions. Just a thought...
 
Also, please explain where the last three column values came from. Thanks.
 
Ct (60min)(PA208460),CT (50min)(WA208460),Attendance(Yes)
Ct (60min)(TX 208460),CT (50min)(TX 208460),Attendance(Yes)

can I convert this line to 2 lines or 3 or 5 etc based on ","
Example
Code: 
State           ID

PA               208460

WA              208460
 
not at all clear what you are asking - Post #4 seems a completely different requirement to post #1

but to answer this question

can I convert this line to 2 lines or 3 or 5 etc based on ","
Click to expand...
yes - but if you can't provide understandable information, I can't provide an understandable solution and don't want to speculate. Best I can suggest is look at the vba split function and/or the instr/instrrev functions combined with the left/mid and right functions, perhaps in conjunction with a union query
 
Is is always exactly in the format you show. In this case after the second ( is the state abbreviation followed by an ID until ")". Same thing after the fourth (
 
MajP said:
Is is always exactly in the format you show. In this case after the second ( is the state abbreviation followed by an ID until ")". Same thing after the fourth (
Click to expand...
yes state followed by ID
 
this will return a 1 Dimensional array {State, ID, [State, ID]}
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
    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]{6,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


Private Sub testGetStates()
    Dim s As String
    Dim var As Variant
    Dim i As Integer
    s = "Ct (60min)(PA208460),CT (50min)(WA208460),Attendance(Yes)"
    var = fnGetState_ID(s)
    If IsNull(var) = False Then
        For i = 0 To UBound(var) Step 2
            Debug.Print var(i), var(i + 1)
        Next
    End If
End Sub
 
arnelgp said:
this will return a 1 Dimensional array {State, ID, [State, ID]}
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
    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]{6,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


Private Sub testGetStates()
    Dim s As String
    Dim var As Variant
    Dim i As Integer
    s = "Ct (60min)(PA208460),CT (50min)(WA208460),Attendance(Yes)"
    var = fnGetState_ID(s)
    If IsNull(var) = False Then
        For i = 0 To UBound(var) Step 2
            Debug.Print var(i), var(i + 1)
        Next
    End If
End Sub
Click to expand...
Great, Wonderful

Thank you so much

if Ct (60min)(PA208460),CT (50min)(WA208460),Attendance(Yes) is column in query how can I use TestGetStates()
I want to pass column to this function and return the value in row as you function do

here is example
Code: 
Public Sub testGetStates1(MyColum As String)
   ' Dim s As String
    Dim var As Variant
    Dim i As Integer
  '  s = "Ct (60min)(PA208460),CT (50min)(WA208460),Attendance(Yes)"
    var = fnGetState_ID(MyColum)
    If IsNull(var) = False Then
        For i = 0 To UBound(var) Step 2
            Debug.Print var(i), var(i + 1)
        Next
    End If
End Sub
 
you create Temp table and Append the values to that table.
use this Temp table instead of your Query.
Code: 
Public Sub testGetStates1(MyColum As String)
   ' Dim s As String
    Dim var As Variant
    Dim i As Integer
  '  s = "Ct (60min)(PA208460),CT (50min)(WA208460),Attendance(Yes)"
    var = fnGetState_ID(MyColum)
    If IsNull(var) = False Then
        For i = 0 To UBound(var) Step 2
            CurrentDb.Execute "Insert Into tempTableName ([State], [ID]) Select '" &  var(i) & "','" &  var(i + 1) & "'"
        Next
    End If
End Sub
 
arnelgp said:
you create Temp table and Append the values to that table.
use this Temp table instead of your Query.
Code: 
Public Sub testGetStates1(MyColum As String)
   ' Dim s As String
    Dim var As Variant
    Dim i As Integer
  '  s = "Ct (60min)(PA208460),CT (50min)(WA208460),Attendance(Yes)"
    var = fnGetState_ID(MyColum)
    If IsNull(var) = False Then
        For i = 0 To UBound(var) Step 2
            CurrentDb.Execute "Insert Into tempTableName ([State], [ID]) Select '" &  var(i) & "','" &  var(i + 1) & "'"
        Next
    End If
End Sub
Click to expand...

When I pass this value
Ct (60min)(PA77077, GA721702NJ032121995),Ct (50min)(PA77077, GA721702NJ032121995),Attendance(Yes)

Not Show PA77077 inside the result
 
zezo2021 said:
When I pass this value
Ct (60min)(PA77077, GA721702NJ032121995),Ct (50min)(PA77077, GA721702NJ032121995),Attendance(Yes)

Not Show PA77077 inside the result
Click to expand...
the code work but look to the above request 👆

NOt Show PA77077
 
this sample example gives me a null value (not extract the ID and state)
CLE Credit (60min)(VA33307),CLE Credit (50min)(VA33307),Attendance(Yes)
CLE Credit (60min)(MA1234),CLE Credit (50min)(MA1234),NASBA CPE(________),Attendance(Yes)
CLE Credit (60min)(VA24322),CLE Credit (50min)(VA24322),Attendance(Yes)
NASBA CPE(VA10224),Attendance(Yes)
CLE Credit (60min)(VA72751),CLE Credit (50min)(VA72751),NASBA CPE(VA24852),Attendance(Yes)
CLE Credit (60min)(PR6877),CLE Credit (50min)(PR6877),Attendance(Yes)
CLE Credit (60min)(WA41304),CLE Credit (50min)(WA41304),Attendance(Yes)
CLE Credit (60min)(MO37436),CLE Credit (50min)(MO37436),Attendance(Yes)
CLE Credit (60min)(PA42332),CLE Credit (50min)(PA42332),Attendance(Yes)
 
What if you change the 6 to a 5
.Pattern = "([A-Z\ ]{2,3})([0-9]{5,})"
does that mess any thing up?
 
Yes work.

I took the test data
Data Data

IDfldData
1​
Ct (60min)(PA208460),CT (50min)(PA208460),Attendance(Yes)
2​
Ct (60min)(PA208460),CT (50min)(PA208460),Attendance(Yes)
3​
Ct (60min)(WA 208460),CT (50min)(WA 208460),Attendance(Yes)
4​
Ct (60min)(WA 208460),CT (50min)(WA 208460),Attendance(Yes)
5​
Ct (60min)(TX 208460),CT (50min)(TX 208460),Attendance(Yes)
6​
Ct (60min)(PA77077, GA721702NJ032121995),Ct (50min)(PA77077, GA721702NJ032121995),Attendance(Yes)
7​
CLE Credit (60min)(VA33307),CLE Credit (50min)(VA33307),Attendance(Yes)
8​
CLE Credit (60min)(MA1234),CLE Credit (50min)(MA1234),NASBA CPE(________),Attendance(Yes)
9​
CLE Credit (60min)(VA24322),CLE Credit (50min)(VA24322),Attendance(Yes)
10​
NASBA CPE(VA10224),Attendance(Yes)
11​
CLE Credit (60min)(VA72751),CLE Credit (50min)(VA72751),NASBA CPE(VA24852),Attendance(Yes)
12​
CLE Credit (60min)(PR6877),CLE Credit (50min)(PR6877),Attendance(Yes)
13​
CLE Credit (60min)(WA41304),CLE Credit (50min)(WA41304),Attendance(Yes)
14​
CLE Credit (60min)(MO37436),CLE Credit (50min)(MO37436),Attendance(Yes)
15​
CLE Credit (60min)(PA42332),CLE Credit (50min)(PA42332),Attendance(Yes
Ran this code an added another field to show which record it came from
Code: 
Private Sub testGetStates()
    Dim s As String
    Dim var As Variant
    Dim i As Integer
    Dim recID As Long
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("data")
    Do While Not rs.EOF
      var = fnGetState_ID(rs!fldData)
      recID = rs!ID
      If IsNull(var) = False Then
        For i = 0 To UBound(var) Step 2
            CurrentDb.Execute "Insert Into tmpTable (RecID, State, ID) Select " & recID & ", '" & var(i) & "','" & var(i + 1) & "';"
        Next
       End If
      rs.MoveNext
    Loop
 End Sub

Got this result in a query
Query1 Query1

data.IDfldDataRecIDStatetmpTable.ID
1​
Ct (60min)(PA208460),CT (50min)(PA208460),Attendance(Yes)
1​
PA208460
1​
Ct (60min)(PA208460),CT (50min)(PA208460),Attendance(Yes)
1​
PA208460
2​
Ct (60min)(PA208460),CT (50min)(PA208460),Attendance(Yes)
2​
PA208460
2​
Ct (60min)(PA208460),CT (50min)(PA208460),Attendance(Yes)
2​
PA208460
3​
Ct (60min)(WA 208460),CT (50min)(WA 208460),Attendance(Yes)
3​
WA208460
3​
Ct (60min)(WA 208460),CT (50min)(WA 208460),Attendance(Yes)
3​
WA208460
4​
Ct (60min)(WA 208460),CT (50min)(WA 208460),Attendance(Yes)
4​
WA208460
4​
Ct (60min)(WA 208460),CT (50min)(WA 208460),Attendance(Yes)
4​
WA208460
5​
Ct (60min)(TX 208460),CT (50min)(TX 208460),Attendance(Yes)
5​
TX208460
5​
Ct (60min)(TX 208460),CT (50min)(TX 208460),Attendance(Yes)
5​
TX208460
6​
Ct (60min)(PA77077, GA721702NJ032121995),Ct (50min)(PA77077, GA721702NJ032121995),Attendance(Yes)
6​
PA77077
6​
Ct (60min)(PA77077, GA721702NJ032121995),Ct (50min)(PA77077, GA721702NJ032121995),Attendance(Yes)
6​
GA721702
6​
Ct (60min)(PA77077, GA721702NJ032121995),Ct (50min)(PA77077, GA721702NJ032121995),Attendance(Yes)
6​
NJ032121995
6​
Ct (60min)(PA77077, GA721702NJ032121995),Ct (50min)(PA77077, GA721702NJ032121995),Attendance(Yes)
6​
PA77077
6​
Ct (60min)(PA77077, GA721702NJ032121995),Ct (50min)(PA77077, GA721702NJ032121995),Attendance(Yes)
6​
GA721702
6​
Ct (60min)(PA77077, GA721702NJ032121995),Ct (50min)(PA77077, GA721702NJ032121995),Attendance(Yes)
6​
NJ032121995
7​
CLE Credit (60min)(VA33307),CLE Credit (50min)(VA33307),Attendance(Yes)
7​
VA33307
7​
CLE Credit (60min)(VA33307),CLE Credit (50min)(VA33307),Attendance(Yes)
7​
VA33307
8​
CLE Credit (60min)(MA1234),CLE Credit (50min)(MA1234),NASBA CPE(________),Attendance(Yes)
8​
MA1234
8​
CLE Credit (60min)(MA1234),CLE Credit (50min)(MA1234),NASBA CPE(________),Attendance(Yes)
8​
MA1234
9​
CLE Credit (60min)(VA24322),CLE Credit (50min)(VA24322),Attendance(Yes)
9​
VA24322
9​
CLE Credit (60min)(VA24322),CLE Credit (50min)(VA24322),Attendance(Yes)
9​
VA24322
10​
NASBA CPE(VA10224),Attendance(Yes)
10​
VA10224
11​
CLE Credit (60min)(VA72751),CLE Credit (50min)(VA72751),NASBA CPE(VA24852),Attendance(Yes)
11​
VA72751
11​
CLE Credit (60min)(VA72751),CLE Credit (50min)(VA72751),NASBA CPE(VA24852),Attendance(Yes)
11​
VA72751
11​
CLE Credit (60min)(VA72751),CLE Credit (50min)(VA72751),NASBA CPE(VA24852),Attendance(Yes)
11​
VA24852
12​
CLE Credit (60min)(PR6877),CLE Credit (50min)(PR6877),Attendance(Yes)
12​
PR6877
12​
CLE Credit (60min)(PR6877),CLE Credit (50min)(PR6877),Attendance(Yes)
12​
PR6877
13​
CLE Credit (60min)(WA41304),CLE Credit (50min)(WA41304),Attendance(Yes)
13​
WA41304
13​
CLE Credit (60min)(WA41304),CLE Credit (50min)(WA41304),Attendance(Yes)
13​
WA41304
14​
CLE Credit (60min)(MO37436),CLE Credit (50min)(MO37436),Attendance(Yes)
14​
MO37436
14​
CLE Credit (60min)(MO37436),CLE Credit (50min)(MO37436),Attendance(Yes)
14​
MO37436
15​
CLE Credit (60min)(PA42332),CLE Credit (50min)(PA42332),Attendance(Yes
15​
PA42332
15​
CLE Credit (60min)(PA42332),CLE Credit (50min)(PA42332),Attendance(Yes
15​
PA42332
I did change from 5 to 4.
 
@arnelgp will have to fully explain the pattern. I am not good at regexp
Code: 
.Pattern = "([A-Z\ ]{2,3})([0-9]{4,})"
I think this is two letters followed by 4 or more numbers. When it was 6 it did not pick up the IDs with less than 5 numbers.
 
It means combi of letter or space (2 to 3 of length) + numbers ( min 4 of length).

The first post only have last 6 digit so i used 6.
It will not extract if less than 6.
 
You must log in or register to reply here.

Similar threads

Z
add to character to string in query
2
Replies
23
Views
1,074
Eugene-LS
Eugene-LS
D
Setting up a conditional column in power query, using values in a separate table to check if they are in 2 columns.
Replies
1
Views
309
arnelgp
arnelgp
J
Use VBA to export form data to Excel, but keep getting "We Found a Problem..." when opening the file in Excel
Replies
5
Views
353
June7
June7
essaytee
[SOLVED] Calculated column not sorting by largest to smallest
Replies
1
Views
207
essaytee
essaytee
S
Solved Weird Visual Bug in my Code that Conditionally Hides Columns
Replies
8
Views
343
SparklySpartan
S

Users who are viewing this thread

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