Extract several values from a column

ChrisC365 

You could solve this with a helper table and VBA in Access. The helper table is called UnwantedText and contains the (pieces of) text you want to remove from IN_CODE:

You can extend this table with any text you want/need.

Next, we define a table (ProjectParsed) for the parsed result:

Note that the three fields together form the primary key.

Assuming that the raw project data is in a (linked) table which is called ProjectRaw (you can name it anything you like, but you have to make a slight modification in the coming VBA code if you name it different), this VBA subroutine removes the unwanted text and parses the 6 digit occurrences in IN_CODE into separate rows/records in the table ProjectParsed:

Sub RemoveAndParse()
  'Declare
  Dim sInCode As String, sSQL As String
  Dim i As Integer
    
  'Switch off warnings
  DoCmd.SetWarnings False
    
  'Remove all earlier records from the result table
  sSQL = "DELETE FROM ProjectParsed"
  DoCmd.RunSQL sSQL
      
  'Open tables Project and UnwantedText as recordsets to loop over
  Dim rsProject As DAO.Recordset
  Set rsProject = CurrentDb.OpenRecordset("ProjectRaw", dbOpenTable)

  Dim rsUnwantedText As DAO.Recordset
  Set rsUnwantedText = CurrentDb.OpenRecordset("UnwantedText", dbOpenTable)

  'Loop over the (raw) projects
  Do Until rsProject.EOF
    sInCode = rsProject!IN_CODE
    
    'Remove all the spaces from IN_CODE
    sInCode = Replace(sInCode, " ", "")
    
    'Remove the unwanted text from IN_CODE
    rsUnwantedText.MoveFirst 'go back to first unwanted text for every cycle
    'Loop over the unwanted text
    Do Until rsUnwantedText.EOF
      sInCode = Replace(sInCode, rsUnwantedText!Unwanted, "") 'remove text (replace it by a blank)
      rsUnwantedText.MoveNext 'move to next unwanted text
    Loop
    
    'Parse the 6 digit codes
    For i = 1 To Len(sInCode) Step 6
      sSQL = "INSERT INTO ProjectParsed VALUES('" & rsProject!PROJECT_ID & "', '" & _
             rsProject!PROJECT_TITLE & "', '" & Mid(sInCode, i, 6) & "')"
      DoCmd.RunSQL sSQL
    Next
    
    rsProject.MoveNext 'move to next project
  Loop
  
  rsUnwantedText.Close
  rsProject.Close
  
  'Switch on warnings again
  DoCmd.SetWarnings True
End Sub

I assume and hope that the comments in the code explain what the subroutine does. You can run the routine from the VBA editor or you can call it from a button in one of your Access forms.

After running it you'll get something in the table ProjectParsed like this:

Hope this does the job. And if you need more clarification, please let me know.

Best wishes,

Tieme