Read Multiple lines in Excel and display number in another cell

Tagged: ,

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Matt Bloomfield Matt Bloomfield 6 months, 2 weeks ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #36460
    Profile photo of Long S
    Long S
    Participant

    I want to be able to read a cell that has multiple lines in that cell and display the number in another cell
    for instance I have
    Host
    Host1.Domain
    Host2.Domain
    Host3.Domain
    Host4.Domain

    Total Devices
    4
    So far I can count up cells but not lines in a cell.

    I tried this:
    for($i = 0; $i -lt $data.Count; $i++)
    {
    # Grab all hostnames from the hosts field
    $hosts = $Data[$i].Host -split "`r?`n"

    #Count machines
    $totaldevices=$Data[i].Count

    # Update original row
    $Data[$i]."Total Devices Affected" = $totaldevices
    }
    $Data |Export-Csv .\ACAS.csv -force

    #36531
    Profile photo of Matt Bloomfield
    Matt Bloomfield
    Participant

    Using the PowerShell Excel module the following code works to count all items in column1 (I'm not very imaginative when it comes to column titles).

    $sheet = Import-Excel F:\__Temp\Book1.xlsx
    
    $total = 0
    
    for ($i = 0; $i -lt $sheet.Length; $i++) {
    
        $total = $total + ($sheet.column1[$i] -split "`n").count
    
    } #end for
    
    Write-Output $total
    
    #Sample data saved as Book1.xlsx
    
    Column1
    --------
    Line 1
    Line 2
    Line 3
    --------
    Line 4
    Line 5
    
Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.