Author Posts

March 12, 2016 at 10:48 am

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

March 14, 2016 at 11:26 am

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