如何按字母大小写/括号/数字分割单元格

Question

我在excel中有一个字典，其中一个完整的字典条目占用一个单元格。我想把这个单元格分成两个单元格，第一个是语言1，第二个是语言2。

下面是一个字典的例子:

---

1。男或正原则，阳2。凸，凸起3。响应感叹词4。恐惧感叹词6.感叹词。满文的一颗牙

A A A偶然反应的感叹词

A I BUKDAN折叠纸的外缘

一个吉军I ACANGGA一个凸起字符的青铜身份令牌用于夜间进入城市

A JILGAN A yang tone in music

A FA SERE ONGGOLO参见afanggala

驱赶鸡或鸟的声音

A TA (om.)骚动的声音

---

我想要的输出:

---

A                 | 1. the male or positive principle, yang 2. convex,
A A               | an interjection of casual response 
A I BUKDAN        | the outside edge of a piece of folded paper 
A JIJUN I ACANGGA | a bronze identification token with raised characters 
A JILGAN          | a yang tone in music 
A FA SERE ONGGOLO | see afanggala 
A SI              | a sound used for driving chickens or birds
A TA              | (onom.) the sound of a commotion

我需要几个条件来分裂这些细胞。分割需要发生在每个条目中第一个出现的数字之前(如在第一个条目中)，在第一个小写字母之前，或者在"("

之前。

在条目的最后一个大写字母之后分割它可能不工作，因为大写字母也存在于"Language 2"列中。

这可能吗?

Answer 1

Sub SeparateUpperCase()
    Dim total As Integer
    Dim i As Integer
    Dim count As Integer
    total = ActiveSheet.UsedRange.Rows.count
    For i = 1 To total
'assume the input is in the 1st column and put output in 2nd and 3rd columns
        count = GetUpperCaseNum(ActiveSheet.Cells(i, 1))
        ActiveSheet.Cells(i, 2) = Trim(Left(ActiveSheet.Cells(i, 1), count))
        ActiveSheet.Cells(i, 3) = Trim(Right(ActiveSheet.Cells(i, 1), Len(ActiveSheet.Cells(i, 1)) - count))
    Next
End Sub
Function GetUpperCaseNum(str As String) As Integer
    Dim i As Integer
    Dim A As Integer, Z As Integer, Space As Integer
    Dim tmp As Integer
    A = Asc("A")
    Z = Asc("Z")
    Space = Asc(" ")
    result = 0
    For i = 1 To Len(str)
        tmp = Asc(Mid(str, i, 1))
        If Not (tmp >= A And tmp <= Z Or tmp = Space) Then 'just count UpperCase and Space
            result = i - 1
            Exit For
        End If
    Next
    GetUpperCaseNum = result
End Function