PSCustomObject members to CSV within single cell

Question

EDIT:

For what it's worth, I was able to achieve something better, I think. Hope it helps people:

Using:

$licenseSearchTerm = 'dyn'
$arrayResults = @()

foreach ($userWithLicense in $usersWithLicense[333..384]) {
    $userLicenses = $userWithLicense | Get-AzureADUserLicenseDetail

    if ( $userLicenses | Where-Object { $_.SkuPartNumber -match $licenseSearchTerm } ) {
        $results = [PSCustomObject]@{
            User = $userWithLicense.UserPrincipalName
            License   = $userLicenses.SkuPartNumber | Select-Object -First 1
        }
        $arrayResults += $results

        $userLicenses[1..$userLicenses.Count] | ForEach-Object {
            $results = [PSCustomObject]@{
                License= $_.SkuPartNumber
            }
            $arrayResults += $results
        }
    }
}

---

ORIGINAL QUESTION: The following code...

$licenseSearchTerm = 'license'
$arrayResults = @()

foreach ($userWithLicense in $usersWithLicense[383..384]) {

    $userLicenses = $userWithLicense | Get-AzureADUserLicenseDetail

    if ( $userLicenses | Where-Object { $_.SkuPartNumber -match $licenseSearchTerm } ) {

        $results = [PSCustomObject]@{
            User = $userWithLicense.UserPrincipalName
            License   = $userLicenses.SkuPartNumber 
        }

        $arrayResults += $results
    }
}

# $arrayResults  | ConvertTo-Csv -NoTypeInformation

$arrayResults | Export-Csv -Path $env:USERPROFILE\Desktop\licenses.csv -NoTypeInformation

outputs in a CSV file:

How do I output it like this:

The $userLicenses.SkuPartNumber object contains multiple members which show up in the console output as one line per item.

I tried $userLicenses.SkuPartNumber -join ', ' for each PSCUstomObject which kinda works but it displays each item like this:

Next, I tried $userLicenses.SkuPartNumber -join [Environment]::NewLine but this produces:

Any idea how to format it to look like in the second picture?

Answer 1

As stated in the comments, Excel has some quirks with respect to dealing with multiline fields that sometimes prevent Excel from correctly round-trip via a csv file. Anyhow, a single [char]10 line-delimiter appears to work (for me) if you simply open (right-clicking the csv file in Windows Explorer and selecting Open with -> Excel) the .\Test.csv resulted from the example below:
_{(I guess that some localization settings might also of importance)}

$usersWithLicense =
    [PSCustomObject]@{ User = 'User1'; License = 'License1', 'License2', 'License3' },
    [PSCustomObject]@{ User = 'User2'; License = 'License4', 'License5', 'License6' }

$usersWithLicense | Foreach-Object {
   $_.License = $_.License -Join [Char]10
   $_
} | Export-Csv .\Test.csv

You might also open the file from your script:

$Excel = New-Object -ComObject Excel.Application
$Null = $Excel.Workbooks.Open("$pwd\Test.csv")
$Excel.Visible = $True