How to split through the whole list using PowerShell

Try this:

# Import csv into an array
$Sites = (Import-Csv C:\temp\Modified-Audit-Log-Records.csv).'SharePoint Site'

# Create Export variable
$Export = @()

# ForEach loop that goes through the SharePoint sites one at a time
ForEach($Site in $Sites){
    
    # Clean up the input to leave only the hyperlink
    $Site = $Site.replace('Include:[','')
    $Site = $Site.replace(']','')

    # Split the hyperlink at the fifth slash (Split uses binary, so 0 would be the first slash)

    $SiteID = $Site.split('/')[4]

    # The 'SharePoint Site' Include:[All] entry will be empty after doing the split, because it has no 4th slash.
    # This If statement will detect if the $Site is 'All' and set the $SiteID as that.

    if($Site -eq 'All'){
        $SiteID = $Site
    }

    # Create variable to export Site ID
    $SiteExport = @()
    $SiteExport = [pscustomobject]@{
        'SharePoint ID' = $SiteID
    }
    
    # Add each SiteExport to the Export array
    $Export += $SiteExport
}

# Write out the export
$Export

You have 3 distinct patterns you are trying to extract data from. I believe regex would be an appropriate tool.

If you are wanting the new csv to just have the single ID column.

$file = "C:\AuditLogSearch\New folder\Modified-Audit-Log-Records.csv"

$IdList = switch -Regex -File ($file){
    'Include:.+(?=/(\w+?)_)(?<=personal)' {$matches.1}
    'Include:(?=\[(\w+)\])' {$matches.1}
    'Include:.+(?=/(\w+?)/)(?<=sites)' {$matches.1}
}

$IdList |
    ConvertFrom-Csv -Header "Sharepoint ID" |
        Export-Csv -Path $newfile -NoTypeInformation

If you want to add a column to your existing CSV

$file = "C:\AuditLogSearch\New folder\Modified-Audit-Log-Records.csv"

$properties = ‘*’,@{
    Name = 'Sharepoint ID'
    Expression = {
        switch -Regex ($_.'sharepoint Site'){
            'Include:.+(?=/(\w+?)_)(?<=personal)' {$matches.1}
            'Include:(?=\[(\w+)\])' {$matches.1}
            'Include:.+(?=/(\w+?)/)(?<=sites)' {$matches.1}
        }
    }
}

Import-Csv -Path $file |
    Select-Object $properties |
        Export-Csv -Path $newfile -NoTypeInformation

Regex details

• .+ Match any amount of any character
• (?=...) Positive look ahead
• (...) Capture group
• \w+ Match one or more word characters
• ? Lazy quantifier
• (?<=...) Positive look behind

This would require more testing to see if it works well, but with the input we have it works, the main concept is to use System.Uri to parse the strings. From what I'm seeing, the segment you are looking for is always the third one [2] and depending on the previous segments, perform a split on _ or trim the trailing / or leave the string as is if IsAbsoluteUri is $false.

$csv = Import-Csv path/to/test.csv

$result = foreach($line in $csv)
{
    $uri = [uri]($line.'SharePoint Site' -replace '^Include:\[|]$')
    
    $id = switch($uri)
    {
        {-not $_.IsAbsoluteUri} {
            $_
            break
        }
        { $_.Segments[1] -eq 'personal/' } {
            $_.Segments[2].Split('_')[0]
            break
        }
        { $_.Segments[1] -eq 'sites/' } {
            $_.Segments[2].TrimEnd('/')
        }
    }

    [pscustomobject]@{
        'SharePoint Site' = $line.'SharePoint Site'
        'SharePoint ID'   = $id 
    }
}

$result | Format-List

A concise solution that appends a Sharepoint ID column to the existing columns by way of a calculated property:

Import-Csv 'C:\AuditLogSearch\New folder\Modified-Audit-Log-Records.csv' | 
  Select-Object *, @{
    Name = 'SharePoint ID'
    Expression = {
      $tokens = $_.'SharePoint Site' -split '[][/]'
      if ($tokens.Count -eq 3) { $tokens[1] } # matches 'Include:[All]'
      else                     { $tokens[5] -replace '_nam_corp_kl_com$' }
    }
  }

Note:

• To see all resulting column values, pipe the above to Format-List.

• To re-export the results to a CSV file, pipe to Export-Csv