如何將句子轉換為單個單詞的列/行

為什么它不起作用？

僅列印第一個單詞，我不知道如何將“For”中的“10”更改為“For each word in sentence”

 Sub Change()
    Dim S As String
    Dim i As Integer
    Dim x As String
    
    
    S = InputBox("Sentence")
    x = Split(S, " ")
For i = 1 To x
 Cells(1, i).Value = Split(S, " ")
 
 Next i
  
End Sub

uj5u.com熱心網友回復：

嘗試這個：

Public Sub Change()
    Dim sentence As String: sentence = InputBox("Sentence")
    Dim col As Long: col = 1
    Dim word As Variant: For Each word In Split(sentence, " ")
        ThisWorkbook.Worksheets("Sheet1").Cells(1, col).Value = word
        col = col   1
    Next
End Sub

uj5u.com熱心網友回復：

將句子拆分為作業表

Option Explicit

Sub SentenceToRow()
    Const ProcTitle As String = "Sentence to Row"
    Const First As String = "A1"
    
    Dim S As Variant: S = InputBox("Input a Sentence", ProcTitle)
    If Len(S) = 0 Then
        MsgBox "You canceled.", vbExclamation, ProcTitle
        Exit Sub
    End If
    
    Dim Strings() As String: Strings = Split(S)
    Dim cCount As Long: cCount = UBound(Strings)   1
        
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim rg As Range: Set rg = ws.Range(First).Resize(, cCount)
    rg.Value = Strings
    
    MsgBox "Sentence split to a row.", vbInformation, ProcTitle

End Sub

Sub SentenceToColumn()
    Const ProcTitle As String = "Sentence to Column"
    Const First As String = "A1"
    
    Dim S As Variant: S = InputBox("Input a Sentence", ProcTitle)
    If Len(S) = 0 Then
        MsgBox "You canceled.", vbExclamation, ProcTitle
        Exit Sub
    End If
    
    Dim Strings() As String: Strings = Split(S)
    Dim rCount As Long: rCount = UBound(Strings)   1
        
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim rg As Range: Set rg = ws.Range(First).Resize(rCount)
    rg.Value = Application.Transpose(Strings)
    
    MsgBox "Sentence split to a column.", vbInformation, ProcTitle

End Sub

uj5u.com熱心網友回復：

這是快速有效的：

Sub testSplit2Row()
    Dim frase As String
    frase = "las palabras de amor"
    
    Dim ary As Variant
    ary = Split(frase, " ")
    
    Dim dest As Range
    Dim start As Range
    Set start = Range("B1")
    Set dest = start.Resize(UBound(ary)   1)
    dest.Value = Application.Transpose(ary)
    
    start.Resize(, UBound(ary)   1).Value = ary
End Sub

uj5u.com熱心網友回復：

如果你處置的動態陣列功能msexcel的365，你可以從以下功能中獲益，可用以及UDF或通過代碼。

該函式words()接受范圍值、陣列值或顯式字串輸入作為第一個引數s；第二個引數IsVertical是可選的，表示默認情況下結果將作為垂直陣列回傳（而不是“平面”陣列）。

Public Function words(ByVal s As Variant, Optional IsVertical As Boolean = True)
    'Debug.Print VarType(s)
    If VarType(s) >= vbArray Then
        s = Replace(Application.WorksheetFunction.ArrayToText(s), ",", "")
    End If
    words = Split(s)
    If IsVertical Then
        words = Application.WorksheetFunction.Transpose(Split(s))
    End If
End Function

a) 使用多行范圍輸入的示例 B2

   =words(A2:A4)

b) 通過代碼呼叫示例

Option Explicit                         ' module head of code module 

Sub ExampleCall
    With Sheet1
        Dim wds As Variant
        wds = words(.Range("A2:A4"))
        .Range("A10").Resize(UBound(wds), UBound(wds, 2)) = wds
    End With
End Sub

但是，如果您打算水平顯示結果，只需撰寫如下代碼（注意尺寸變化！）：

    '...
    wds = words(.Range("a2:a4"), False)         ' False returns "flat" 1-dim array
    .Range("A10").Resize(1, UBound(wds)) = wds

標籤：擅长 vba

上一篇：為什么我在使用ifs函式時在excel中出現值錯誤？

下一篇：如何將單元格值作為引數提供給MSExcel中的ROW()公式？