How convert sentences to column/row of single words

user_17390249 0
Viewed 97

Why is it not working?

Prints only the first word and I don't know how I can change "10" in "For" on something like "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
vba excel
4 Answers

Try this:

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
user_63067820

Split Sentence to Worksheet

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
user_98140690

this is fast and eficient:

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
user_117015100

If you dispose of the dynamic array functions of MSExcel 365, you might profit from the following function, usable as well as udf or via code.

The function words() accepts range values, array values or explicit string inputs as first argument s; the second argument IsVertical is optional and indicates that results will be returned as vertical array by default (instead of a "flat" array).

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) Example using a multi-row range input in B2

   =words(A2:A4)

udf

b) Example call via code

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

If you intend, however to display results horizontally, just code as follows (note the dimension change!):

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