在分组的单词中识别大写字母并插入逗号和空格

解决方法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 SubPublic 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 FunctionPublic 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_resultEnd FunctionI 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 ValleyConsumer InternetMobileB2BEnterprise SoftwareE-CommerceMarketplacesSocialLet 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 ValleyConsumer Internet, Mobile, B2B, Enterprise Software, E-Commerce, Marketplaces, SocialWhen 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:Identify a capital letter which is not after a space and has small letter previous to it.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 SubPublic 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_resultEnd FunctionThe 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 B2BThe 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 SubPublic 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 FunctionPublic 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_resultEnd Function 这篇关于在分组的单词中识别大写字母并插入逗号和空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！