将文本分割为Excel中的列
我在Excel
中有一列,其中包含以下格式的数据:“NAME OF BAND Album Title”。我想将这个单元分成两个 - 一个用于全部大写的乐队名称,另一个用于专辑标题。以下是一些数据示例:将文本分割为Excel中的列
Column A
ABSORBED Demo '98
ABSTRACT CELL THEORY Act
ABSTRACT SATAN Elite 7512
ABSTRACT SATAN Aryan Blitzkrieg Union
ABSTRACT SATAN Satanic Blood Circle
ABSTRACT SHADOWS Symphony of Hakel
按空格分割不起作用,因为乐队的名字中有不同数量的单词。任何帮助,将不胜感激。
这是一个公式解决方案。不需要VBA。
假设你的列表开始于单元格A1,在单元格B1输入以下公式:
=LEFT(A1,MATCH(,--(CODE(MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1))<96),)-3)
这是一个阵列式,并且必须以Ctrl键 + 移位来确认 + 输入 。
然后在小区C1,输入:
=MID(A1,MATCH(,--(CODE(MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1))<96),)-1,99)
这是一个阵列式,并且必须以Ctrl键 + 移 + 输入来确认。
现在选择范围B1:C1,并根据需要向下复制。
。
以下是他们是如何工作的。我们将讨论第一个公式。
MID函数将单元格A1的值拆分为单个字符。 CODE函数返回每个字符的ASCII码号码。我们测试每个代码的编号,看它是否小于96,这是第一个小写字母“a”。
这给了我们一个布尔值(真或假)的数组,一个单元格A1中每个char的Booelan值。
我们通过双重一元( - )将布尔转换为ONES和ZEROES。
我们使用MATCH函数在数组中搜索第一个ZERO的位置。
连续大写字母的结尾是由MATCH返回的位置之前的三个char位置。
就是这样。
什么使得所有可能的是数组在ROW/OFFSET组合产生的公式的核心。结合LEN函数,该组合生成一个类似{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}的向量数组。该数组的最后一个和最大数字等于单元格A1中值的长度。
。
UPDATE
这里正显示出这些公式样本的工作簿上的问题的示例数据工作:http://www.excelhero.com/samples/torentino_excelhero.xlsx
像这样的东西可能会从专辑中分割乐队名称。
Sub splt()
Dim rw As Long, p As Long, r As Long, sp As Long, v As Long
Dim bnd As String, ttl As String, tmp As Variant
With Worksheets("Sheet1")
For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
p = 1
bnd = vbNullString
ttl = Trim(.Cells(rw, 1).Value2) & Chr(32)
tmp = Split(ttl)
For v = LBound(tmp) To UBound(tmp)
If UCase(tmp(v)) = tmp(v) Then
bnd = Join(Array(bnd, tmp(v)), Chr(32))
Else
Exit For
End If
Next v
.Cells(rw, 2) = bnd
.Cells(rw, 3) = Trim(Right(ttl, Len(ttl) - Len(bnd)))
Next rw
End With
End Sub
嗨Excel的英雄,你能解释一下在B1公式?我无法理解逻辑。谢谢。 – Sergio
@Sergio请参阅我的答案的更新下半部分的解释。 –
@ExcelHero谢谢但由此产生的分裂无法正常工作。我结束了两个单元格,但原始单元格中的BAND NAME专辑标题在一个单元格中转换为BD NA,而另一个单元格中没有专辑标题。 – torentino