多个运单号整理成单个的运单号

minstatey

hcm19522 发表于 2022-12-12 13:42
數據依3樓 字首數字有誤=MID(CLEAN(PHONETIC(B$2:B$8)),ROW(A1)*4-3,4)

试了一下还是不行

tmplinshi

以下是 PowerShell 代码，执行后会将处理过的整列数据复制到剪贴板

1. $xl = [Runtime.Interopservices.Marshal]::GetActiveObject('Excel.Application'); $xl.Range("a1").CurrentRegion.Value2 -replace '.{15}\B', "`$0`n" | Set-Clipboard

复制代码

或者直接写入到 D 列:

1. $xl = [Runtime.Interopservices.Marshal]::GetActiveObject('Excel.Application');
   
2. $newData = $xl.Range("a1").CurrentRegion.Value2 -replace '.{15}\B', "`$0`n" -split "`n"
   
3. 
   
4. $safeArr = New-Object 'object[,]' $newData.Count, 1
   
5. $i = 0
   
6. foreach ($v in $newData) {
   
7.         $safeArr[$i++, 0] = $v
   
8. }
   
9. 
   
10. $rng = $xl.Range("d1").Resize($safeArr.Count)
    
11. $rng.ClearFormats()
    
12. $rng.NumberFormatLocal = "@"
    
13. $rng.value2 = $safeArr

复制代码