Existing data cell not being cleared from Excel sheet

I have a data sheet in Excel that I'm trying to manipulate the data with. One of the outcomes is that one cell should be cleared out and copy/pasted (or cut/pasted) into another cell as desired.

Below is the code I'm using to manipulate the sheet with:

In this case I'm trying to merge a column named Target into Destination

$SomeSheet   = "C:\Some\Path\lorem.xlsx"
$MasterDBSheetName  = "Database"
$MasterDBSheet  = Import-Excel -Path $MasterDBPath -WorksheetName $MasterDBSheetName

foreach ($dbRow in $MasterDBSheet) {
    $row_target = $dbRow.'Target"
    $row_destination = $dbRow.'Destination'

    $merge = $row_target + " " + $row_destination

    $dbRow.'Target' = $null
    $dbRow."Destination' = $merge
}

$MasterDBSheet | Export-Excel -Path $MasterDBPath -WorksheetName $MasterDBSheetName

The data is being merged into "Destination" for each row, but despite being set as $null or "", the original data for Target is still in the same column even though it should be cleared.

I have managed to clear everything if I used —ClearWorksheet, but I was hoping to keep the formatting of the first row at least.

Solution

Assuming you know what Column Index is the Target column, you could try with Set-ExcelColumn setting the -Value to null:

$SomeSheet = 'C:\Some\Path\lorem.xlsx'
$MasterDBSheetName = 'Database'
$pkg = Open-ExcelPackage -Path $SomeSheet
$content = Import-Excel -ExcelPackage $pkg -WorksheetName $MasterDBSheetName
foreach ($dbRow in $content) {
    $dbRow.Destination = $dbRow.Target + ' ' + $dbRow.Destination
    $dbRow.Target = $null
}

# here you set the value of the desired column to null
$setExcelColumnSplat = @{
    ExcelPackage  = $pkg
    WorksheetName = $MasterDBSheetName
    # assuming `Destination` is the first column
    Column        = 1
    # assuming the data starts on row 1
    StartRow      = 1
    Value         = $null
}
Set-ExcelColumn @setExcelColumnSplat
# then export the updated worksheet
$MasterDBSheet | Export-Excel -ExcelPackage $pkg -WorksheetName $MasterDBSheetName