我正在將 Excel 單元格中的字串中的單詞提取到集合中，以便可以拆分為多列。有些單詞之間有多個空格。在這種情況下，我想在提取的拆分范圍中插入一個空白列，換句話說，我想在我的集合中插入一個空白項。我可以很好地提取單詞，但由于某種原因，我無法正確評估具有多個空格的實體。VBA 似乎將任何單個空格解釋為多個空格。
我知道 Range 有 TextToColumns 方法，但它不適合我的需要。
這是字串中的一個小子集：
Shift 2 Shift 1 Shift 2 Shift 1 Shift 2 Shift 1 Shift 2 Week 21 Week 22 Week 23 Week 24 Week 25 Week 26 Week 27 Week 28 Week 29 Week 30 Week 33 Week 34 Week 35 Week 27 Week 28 Week 29 Week 30 Week 33 Week 34 Week 35 Week 27 Week 28 Week 29 Week 30 Week 33 Week 34 Week 35 Week 27 Week 28 Week 29 Week 30 Week 33 Week 34 Week 35
該子集的預期輸出將是以下專案的集合。第 35 周和第 27 周之間有一個空字串：
“班次 2”、“班次 1”、“班次 2”……“第 34 周”、“第 35 周”、“”、“第 27 周”、“第 28 周”……
我嘗試使用 InStr 來查找多個空格： 'secondCaracterPos' 值的增加是因為它是回圈的一部分。
If InStr(secondCharacterPos, multioutput(Range("a3")), " ") Then secondDateCollection.Add " ": secondCharacterPos = secondCharacterPos 10
也：
If InStr(secondCharacterPos, multioutput(Range("a3")), " ") And InStr(secondCharacterPos 1, multioutput(Range("a3")), " ") _
And InStr(secondCharacterPos 2, multioutput(Range("a3")), " ") And InStr(secondCharacterPos 3, multioutput(Range("a3")), " ") _
And InStr(secondCharacterPos 4, multioutput(Range("a3")), " ") And InStr(secondCharacterPos 5, multioutput(Range("a3")), " ") _
And InStr(secondCharacterPos 6, multioutput(Range("a3")), " ") And InStr(secondCharacterPos 7, multioutput(Range("a3")), " ") _
And InStr(secondCharacterPos 8, multioutput(Range("a3")), " ") And InStr(secondCharacterPos 9, multioutput(Range("a3")), " ") _
Then secondDateCollection.Add " ": secondCharacterPos = secondCharacterPos 10
我還嘗試過使用上述短格式和長格式的 MID。
我懷疑我忽略了一些非常小的東西。任何幫助將非常感激。
uj5u.com熱心網友回復：
這似乎有效：
s = "Shift 2 Shift 1 Shift 2 Shift 1 Shift 2 Shift 1 Shift 2 Week 21 Week 22 Week 23 Week 24 Week 25 Week 26 Week 27 Week 28 Week 29 Week 30 Week 33 Week 34 Week 35 Week 27 Week 28 Week 29 Week 30 Week 33 Week 34 Week 35 Week 27 Week 28 Week 29 Week 30 Week 33 Week 34 Week 35 Week 27 Week 28 Week 29 Week 30 Week 33 Week 34 Week 35"
? """" & Replace(Replace(s, Space(11), ""","" "","""), Space(2), """,""") & """"
"Shift 2","Shift 1","Shift 2","Shift 1","Shift 2","Shift 1","Shift 2","Week 21","Week 22","Week 23","Week 24","Week 25","Week 26","Week 27","Week 28 Week 29","Week 30","Week 33","Week 34","Week 35"," ","Week 27","Week 28 Week 29","Week 30","Week 33","Week 34","Week 35"," ","Week 27","Week 28 Week 29","Week 30","Week 33","Week 34","Week 35"," ","Week 27","Week 28 Week 29","Week 30","Week 33","Week 34","Week 35"
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/458963.html
上一篇：VBA陣列字串匹配