在分组的单词中识别大写字母并插入逗号和空格 [英] Identify Capital Letter in grouped words and insert a comma and space

问题描述

我正在执行一项任务，我必须将网站上的内容复制/粘贴到excel中.

但是问题是当我在excel中复制/粘贴内容时，它看起来像这样:

• 洛杉矶纽约硅谷
• 消费互联网MobileB2BEnterprise SoftwareE-CommerceMarketplacesSocial

Let s call Los Angeles an item which is merged with another item New York，我想将这些项目分开，以使信息可读性如下:

• 洛杉矶，纽约，硅谷
• 消费互联网，移动，B2B，企业软件，电子商务，市场，社交

当我注意到我实际上意识到在网站上(由于某种技术原因)，我无法复制项目之间的逗号，因此，其他所有项目都与前一个项目的大写字母合并了.

现在，因为有数百个条目，所以请帮助我知道是否有解决该问题的智能方法.我看到的是可以解决此问题的方式:

1. 标识大写字母，该大写字母不在空格之后，并且在其前面带有小写字母.
2. 在该位置插入逗号和空格，然后继续剩余的字符串.

如果无法解决问题，并且有替代解决方案，请随时进行详细说明. VBA代码/Excel公式-可以帮助我使其自动化的任何内容.谢谢.

解决方案

使用"B2B"会更困难一些，但与其他工具很好地兼容:

Public Sub TestMe()

    Debug.Print insert_a_space("Los AngelesNew YorkSilicon Valley")
    Debug.Print insert_a_space("Consumer InternetMobileB2BEnterprise SoftwareE-CommerceMarketplacesSocial")

End Sub

Public Function insert_a_space(my_str As String)

    Dim my_char         As String
    Dim l_counter       As Long
    Dim str_result      As String

    For l_counter = 1 To Len(my_str)
        my_char = Mid(my_str, l_counter, 1)

        If Asc(my_char) >= 65 And Asc(my_char) <= 90 Then
            If l_counter > 1 Then
                If Asc(Mid(my_str, (l_counter - 1), 1)) <> 32 And _
                Asc(Mid(my_str, (l_counter - 1), 1)) <> 45 Then
                    str_result = str_result & ", "
                End If
            End If
        End If
        str_result = str_result & my_char
    Next l_counter
    insert_a_space = str_result

End Function

逻辑是您运行TestMe.或用作Excel函数insert_a_space，然后提供字符串.该函数查找大写字母(介于65至90 asc之间)，如果大写字母(asc 32)和(asc 45)之间没有空格或-，则会在答案中写一个带空格的逗号.

解决方法SaaS和B2B 这个想法是引入一个逃生符号.因此，无论何时我们看到"\"，我们都会忽略它.此转义符号是通过str_replace_me引入的，应明确为其指定选项.

Public Sub TestMe()

    Dim str_1   As String
    Dim str_2   As String

    str_1 = "Los AngelesNew YorkSilicon Valley"
    str_2 = "Consumer InternetMobileB2BEnterprise SoftwareE-CommerceMarketplacesSocialSaaS"

    Debug.Print insert_a_space(str_replace_me(str_1))
    Debug.Print insert_a_space(str_replace_me(str_2))

End Sub

Public Function str_replace_me(my_str As String) As String

    str_replace_me = Replace(my_str, "SaaS", "Saa\S")
    str_replace_me = Replace(str_replace_me, "B2B", "B2\B")

End Function

Public Function insert_a_space(my_str As String)

    Dim my_char         As String
    Dim l_counter       As Long
    Dim str_result      As String

    For l_counter = 1 To Len(my_str)
        my_char = Mid(my_str, l_counter, 1)

        If Asc(my_char) >= 65 And Asc(my_char) <= 90 Then
            If l_counter > 1 Then
                If Asc(Mid(my_str, (l_counter - 1), 1)) <> 32 And _
                    Asc(Mid(my_str, (l_counter - 1), 1)) <> 45 And _
                    Asc(Mid(my_str, (l_counter - 1), 1)) <> 92 Then

                    str_result = str_result & ", "

                End If
            End If
        End If
        str_result = str_result & my_char
    Next l_counter


    str_result = Replace(str_result, "\", "")
    insert_a_space = str_result

End Function

I am working on a task where I have to copy/paste the content from website into excel.

But the problem is when I copy/paste the content in excel, it appears like this :

• Los AngelesNew YorkSilicon Valley
• Consumer InternetMobileB2BEnterprise SoftwareE-CommerceMarketplacesSocial

Let s call Los Angeles an item which is merged with another item New York and I want to separate these items so that information is readable like this:

• Los Angeles, New York, Silicon Valley
• Consumer Internet, Mobile, B2B, Enterprise Software, E-Commerce, Marketplaces, Social

When I noticed I actually realized that on website (due to some technical reason) I was unable to copy the comma between items and therefore every other item was merged with a capital letter with previous item.

Now please help me know is there an intelligent way to solve this problem because there are hundred of entries. What I see is this is how this problem can be solved:

1. Identify a capital letter which is not after a space and has small letter previous to it.
2. Insert a comma and space at that place and continue with the remaining string.

Please feel free to elaborate if this won't work and if there is an alternative solution. VBA code/ Excel Formula - anything that can help me automate it. Thanks.

解决方案

With "B2B" it would be a bit tougher, but it works pretty well with the others:

Public Sub TestMe()

    Debug.Print insert_a_space("Los AngelesNew YorkSilicon Valley")
    Debug.Print insert_a_space("Consumer InternetMobileB2BEnterprise SoftwareE-CommerceMarketplacesSocial")

End Sub

Public Function insert_a_space(my_str As String)

    Dim my_char         As String
    Dim l_counter       As Long
    Dim str_result      As String

    For l_counter = 1 To Len(my_str)
        my_char = Mid(my_str, l_counter, 1)

        If Asc(my_char) >= 65 And Asc(my_char) <= 90 Then
            If l_counter > 1 Then
                If Asc(Mid(my_str, (l_counter - 1), 1)) <> 32 And _
                Asc(Mid(my_str, (l_counter - 1), 1)) <> 45 Then
                    str_result = str_result & ", "
                End If
            End If
        End If
        str_result = str_result & my_char
    Next l_counter
    insert_a_space = str_result

End Function

The logic is that you run TestMe. Or use as an Excel function insert_a_space and then give the string. The function looks for big letters (between 65 and 90 asc) and if there is no space or - before the big letter (asc 32) and (asc 45), it writes a comma with a space to the answer.

Edit: Workaround SaaS and B2B The idea is to introduce an escape symbol. Thus, whenever we see "\" we ignore it. This escape symbol is introduced through str_replace_me and should be explicitly written for which options it is.

Public Sub TestMe()

    Dim str_1   As String
    Dim str_2   As String

    str_1 = "Los AngelesNew YorkSilicon Valley"
    str_2 = "Consumer InternetMobileB2BEnterprise SoftwareE-CommerceMarketplacesSocialSaaS"

    Debug.Print insert_a_space(str_replace_me(str_1))
    Debug.Print insert_a_space(str_replace_me(str_2))

End Sub

Public Function str_replace_me(my_str As String) As String

    str_replace_me = Replace(my_str, "SaaS", "Saa\S")
    str_replace_me = Replace(str_replace_me, "B2B", "B2\B")

End Function

Public Function insert_a_space(my_str As String)

    Dim my_char         As String
    Dim l_counter       As Long
    Dim str_result      As String

    For l_counter = 1 To Len(my_str)
        my_char = Mid(my_str, l_counter, 1)

        If Asc(my_char) >= 65 And Asc(my_char) <= 90 Then
            If l_counter > 1 Then
                If Asc(Mid(my_str, (l_counter - 1), 1)) <> 32 And _
                    Asc(Mid(my_str, (l_counter - 1), 1)) <> 45 And _
                    Asc(Mid(my_str, (l_counter - 1), 1)) <> 92 Then

                    str_result = str_result & ", "

                End If
            End If
        End If
        str_result = str_result & my_char
    Next l_counter


    str_result = Replace(str_result, "\", "")
    insert_a_space = str_result

End Function

这篇关于在分组的单词中识别大写字母并插入逗号和空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！