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  Matt Bloomfield 1 year, 5 months ago.

  • Author
    Posts
  • #36460

    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

    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
    

You must be logged in to reply to this topic.