如何将第一列的内容拆分成下面 Excel 中的 3 列:
.plnt201218P52.5 PLNT 201218 P52.5
.cyh201106P8 CYH 201106 P8
.w201106P225 W 201106 P225
.w201106C226 W 201106 C226
答案1
答案2
选择要解析的单元格并运行这个简短的 VBA 宏:
Sub ParseWeirdData()
Dim cell As Range, v As String
Dim L As Long, i As Long, CH As String
For Each cell In Selection
v = Mid(cell.Value, 2)
L = Len(v)
For i = 1 To L
CH = Mid(v, i, 1)
If IsNumeric(CH) Then
Exit For
End If
Next i
cell.Offset(0, 1).Value = Mid(v, 1, i - 1)
cell.Offset(0, 2).Value = Mid(v, i, 6)
cell.Offset(0, 3).Value = Mid(v, i + 6, 99)
Next cell
End Sub
此代码假定解析中的第二项是六位数字。
怎么运行的:
代码寻找第一个数字特点。
- 第一项是第一个数字字符左侧的所有内容
- 第二项是第一个数字字符和随后的 5 个字符
- 第三项是第二项之后的所有内容
编辑#1:
避免使用 VBA 非常简单。关键是获取第一个数字。。 在E1进入:
=MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1 & "0123456789",1))
并向下复制(此“辅助”列给出了第一个数字字符的位置)。
在B1进入:
=MID(A1,2,E1-2)
并向下复制。在C1进入:
=MID(A1,E1,6)
并向下复制。在D1进入:
=MID(A1,E1+6,99)
并向下复制:
有列埃数据使得所有其他公式变得非常简单!
答案3
只是为了好玩,如果有人有 Excel O365,您可以使用以下内容B1
:
=TRANSPOSE(FILTERXML("<t><s>"&LET(B,MATCH(58,CODE(MID(A1,SEQUENCE(LEN(A1),,2),1)),-1)+1,C,B+6,REPLACE(REPLACE(MID(A1,2,LEN(A1)),C,0,"</s><s>"),B,0,"</s><s>"))&"</s></t>","//s"))
或者:
=TRANSPOSE(FILTERXML("<t><s>"&LET(PRT,MATCH(58,CODE(MID(A1,SEQUENCE(LEN(A1),,2),1)),-1),TEXTJOIN("</s><s>",,MID(A1,2,PRT),MID(A1,PRT+2,6),MID(A1,8+PRT,99)))&"</s></t>","//s"))
现在将公式拖至B4
。
我将进一步解释第二个公式,因为它在代码语言中比第一个公式多出 2 个字符!
公式的核心是LET()
,目前在 Excel O365 中可用,旨在保存自定义变量以供进一步计算。在函数的第一个参数中,我们为第一个变量指定一个自定义名称,在本例中为“PRT”。在第二个参数中,我们计算此变量。由于我们使用的是 Excel O365,我习惯SEQUENCE()
使用从第二个字符开始分离整个字符串MID()
,CODE()
并MATCH()
返回大于 ASCII 代码 58 的第一个字符的位置。您可以看到我为什么这样做这里。
现在,由于我们已经将位置绑定到变量,我们可以在函数的第三个参数中使用这个变量LET()
;我们的计算!在第二个公式的情况下,我用来TEXTJOIN()
创建一个有效的XML
格式化字符串。我想这样做是因为我可以用来FILTERXML()
从字符串返回一个值数组,可以说是一种“拆分”函数。有关更多信息,我想请您参阅这StackoverFlow 上的问答。
最后一步是将TRANSPOSE()
返回的数组分解成列B:D
。
答案4
编辑:这不适用于原始问题(见评论)。将其作为谷歌搜索“如何在 Excel 中拆分字符串”以获得无公式解决方案的替代方案。这仅在输入数据可以沿相同字符位置(固定长度)切割时才有效。
突出显示 A 列,单击数据 > 文本到列,然后仔细按照提示操作。Excel 会按固定宽度(即每 10 个字符,然后每 5 个字符等)或按分隔符(即在空格或隐藏的制表符后 - 看起来像普通空格字符,因此请尝试一下)拆分数据。