有這個 VBA 代碼，它在第一個空格后洗掉一切
嘗試修改它，以便在 5 個空格后洗掉所有內容，而不是在整個 sheet1 單元格中或將結果發布到 sheet 2 中，任何一種方式都可以
例如
從
今天天氣不錯，就是有點冷
至
今天是個好日子
Sub Test()
Dim X As Long, Uniques() As String, Obj As Object
Dim Data As Variant, Results As Variant, ObjKeys() As String
Application.ScreenUpdating = False
Data = Range("H2", Cells(Rows.Count, "H").End(xlUp)).Value
For X = 1 To UBound(Data)
Data(X, 1) = Left(Data(X, 1), InStr(Data(X, 1) & " ", " ") - 1)
Next
Set Obj = CreateObject("Scripting.Dictionary")
With CreateObject("Scripting.Dictionary")
For X = 1 To UBound(Data)
Obj.Item(CStr(Data(X, 1))) = 1 ' CStr(Data(X, 1))
Next
ObjKeys = Split(Join(Obj.keys))
ReDim Results(1 To UBound(ObjKeys) 1, 1 To 1)
For X = 0 To UBound(ObjKeys)
Results(X 1, 1) = ObjKeys(X)
Next
Range("H2").Resize(UBound(Data)) = Data
Sheets("Sheet2").Range("J1").Value = Range("H1").Value
Sheets("Sheet2").Range("J2").Resize(UBound(Results)) = Results
End With
Application.ScreenUpdating = True
End Sub
uj5u.com熱心網友回復：
你可以這樣做：
Const NUM_SPACES As Long = 5
Dim s As String, arr
s = "It was a good day today but a little cold"
arr = Split(s, " ", NUM_SPACES 2) 'split to max 7 elements
'remove anything in the last position (arr starts at zero)
If UBound(arr) = NUM_SPACES 1 Then arr(NUM_SPACES 1) = ""
Debug.Print Join(arr, " ") '> It was a good day today
uj5u.com熱心網友回復：
不需要 VBA 的替代解決方案：
=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",100)),500))
這會將每個空格替換為 100 個空格。獲取前 500 個（5*100 個空格）字符。然后洗掉所有額外的空格。
uj5u.com熱心網友回復：
如果您擁有具有最新功能的 Office 365，只需：
=TEXTBEFORE(A1," ",6)
如果沒有足夠的分隔符，將回傳錯誤。如果有可能，您可能需要指定會發生什么。
這也可以使用 Windows Excel 2010 和 Excel 365（Windows 或 Mac）中提供的 Power Query 來完成
使用 Power Query
- 在資料表中選擇一些單元格
Data => Get&Transform => from Table/Range或者from within sheet- 當 PQ 編輯器打開時：
Home => Advanced Editor - 記下第 2 行中的表名
- 粘貼下面的 M 代碼代替您看到的內容
- 將第 2 行中的表名稱更改回最初生成的名稱。
- 閱讀評論并探索
Applied Steps以了解演算法
同樣，如果沒有五個空格，您沒有指定所需的內容，因此如果空格少于五個，此例程將回傳整個字串
let
Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
//create list of column headers
colNames = Table.ColumnNames(Source),
//set all data types to text
#"Changed Type" = Table.TransformColumnTypes(Source,
List.Transform(colNames, each {_, type text})),
//Remove all after the fifth space (including that space)
#"Trim Strings" = Table.TransformColumns(#"Changed Type",
List.Transform(colNames, (cn)=> {cn, each try Text.Combine(List.FirstN(Text.Split(_," "),5)," ") otherwise null}))
in
#"Trim Strings"
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/518024.html
標籤：擅长vba