提问人：Eck 提问时间：10/17/2023 最后编辑：EnigmativityEck 更新时间：10/17/2023 访问量：70
将字符串拆分为 2D 变体数组
Split String Into 2D Variant Array
问：
我有一个示例单元格，其值为一个长字符串：
1;#SCAN035545,2;#SCAN025196,3;#SCAN025192,4;#SCAN020504,5;#SCAN002824,6;#SCAN025354,7;#SCAN037102,8;#SCAN035977,9;#SCAN035763,10;#SCAN032932,11;#SCAN025000,12;#SCAN019935,13;#SCAN039383,14;#SCAN039376,15;#SCAN028043,16;#SCAN999999,17;#SCAN039427,18;#SCAN039419,19;#SCAN039188,20;#SCAN029674,21;#SCAN042707
其中“;”和“是分隔符。
我最终需要： 找到某个术语 （SCAN999999）;
- 删除它及其随附的编号（16）;
- 重新编号所有剩余代码（15;#SCAN028043,16;#SCAN999999,17;#SCAN039427,18;#SCAN039419-> 15;#SCAN028043,16;#SCAN039427,17;#SCAN039419）;
- 将所有剩余的项重新连接到一个字符串中，如原始 （1;#SCAN035545,2;#SCAN025196,3;#SCAN025192 等）
然后，这将遍历下一个单元格，依此类推。我需要对动态数量的单元格和动态长度的字符串（可以进入数百个分隔符）执行此操作。这只是为了明确这一点，这需要相当动态。
我坚持什么：1 和 2
我认为处理数据的最佳方法是将字符串转换为具有 2 列的数组或表，如下所示： , ρ#SCAN035545 2 |#SCAN025196 3 |#SCAN025192
这是否可行，这是要走的路吗？如果我使用数组，我将需要一个 Variant 类型，但我不确定如何将字符串拆分为 Variant 数组，因为拆分代码仅适用于 String 数组。
如果我使用一个表（带有一个可以重置 # 行的 listobject 表），我担心它可能会使一切变得迟钝。
答：
0赞
Enigmativity
10/17/2023
#1
这对我有用：
=LET(
input,
INDEX(TEXTSPLIT(A1,";",","),,2),
middle,
FILTER(input,input<>"#SCAN999999"),
output,
MAKEARRAY(ROWS(middle),2,LAMBDA(_r,_c,IF(_c=1,_r,INDEX(middle,_r,1)))),
TEXTJOIN(",",,BYROW(output,LAMBDA(_r,TEXTJOIN(";",,_r))))
)
输入：
1;#SCAN035545,2;#SCAN025196,3;#SCAN025192,4;#SCAN020504,5;#SCAN002824,6;#SCAN025354,7;#SCAN037102,8;#SCAN035977,9;#SCAN035763,10;#SCAN032932,11;#SCAN025000,12;#SCAN019935,13;#SCAN039383,14;#SCAN039376,15;#SCAN028043,16;#SCAN999999,17;#SCAN039427,18;#SCAN039419,19;#SCAN039188,20;#SCAN029674,21;#SCAN042707
输出：
1;#SCAN035545,2;#SCAN025196,3;#SCAN025192,4;#SCAN020504,5;#SCAN002824,6;#SCAN025354,7;#SCAN037102,8;#SCAN035977,9;#SCAN035763,10;#SCAN032932,11;#SCAN025000,12;#SCAN019935,13;#SCAN039383,14;#SCAN039376,15;#SCAN028043,16;#SCAN039427,17;#SCAN039419,18;#SCAN039188,19;#SCAN029674,20;#SCAN042707
2赞
taller
10/17/2023
#2
- 从 A 列加载输入字符串
- 将结果保存在 B 列上
Option Explicit
Sub demo()
Dim i As Long, j As Long
Dim arrRes(), arrData, arr1, arr2, arr1res()
Dim rngData As Range, iIndex As Long
Const KEYWORD = "#SCAN999999" ' search term start with #
' Load data into an array from column A (modify as needed)
Set rngData = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
arrData = rngData.Value
ReDim arrRes(1 To UBound(arrData), 1 To 1)
For i = LBound(arrData) To UBound(arrData)
' KEYWORD matching
If InStr(arrData(i, 1), KEYWORD) > 0 Then
' Split by ","
arr1 = Split(arrData(i, 1), ",")
ReDim arr1res(LBound(arr1) To UBound(arr1))
iIndex = 1
For j = LBound(arr1) To UBound(arr1)
' Split by ","
arr2 = Split(arr1(j), ";")
If UCase(arr2(1)) = KEYWORD Then
arr1(j) = "" ' Skip matching term
Else
arr2(0) = CStr(iIndex)
arr1res(iIndex - 1) = Join(arr2, ";")
iIndex = iIndex + 1
End If
Next j
ReDim Preserve arr1res(LBound(arr1) To iIndex - 2)
arrRes(i, 1) = Join(arr1res, ",")
Else
arrRes(i, 1) = arrData(i, 1)
End If
Next i
' Update cells on column B
rngData.Offset(0, 1).Value = arrRes
End Sub
评论
0赞
Eck
10/17/2023
非常感谢！两天多来，我一直在制作过于复杂的代码，这看起来非常漂亮和简洁。这很有帮助，谢谢。
上一个：将数组写入已筛选的范围
评论