CODEWITHSUNDEEP
excelexcel-formula
Jian Yang
Jian Yang

Reputation: 165

How to generate words from excel cell using space delineation?

Is there a way to generate a list of space delineated words from an excel cell using a formula? For example, if I have the string:

"My name is Jack"

It should generate the words: "My", "name", "is", "Jack" in different cells.

But it should also not be dependent on the length of the string. So for example:

"I love squirrels"

Should generate the words: "I", "love", "squirrels" with the same formula.

Upvotes: 0

Views: 193

Answers (2)

Dan Donoghue
Dan Donoghue

Reputation: 6206

How about a UDF?

Put this in a module in the workbook:

Function WordInString(MyWord As String, WordNum As Long)
    If Len(MyWord) - Len(Replace(MyWord, " ", "")) < WordNum - 1 Then
        WordInString = ""
    Else
        WordInString = Split(MyWord, " ")(WordNum - 1)
    End If
End Function

Assuming your data is in column A row 1 and you want to start populating from column B onwards enter this formula in B1

=WordInString($A1,COLUMN(A1))

Drag across and down

Alternatively if you absolutely positively want a native Excel formula, try this:

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",255)),((COLUMN(A1)-1)*255)+1,255))

Same deal, assumes data starts in A1, put this in B1 and drag across and down.

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

With your sentence in A1

B1:  =TRIM(MID(SUBSTITUTE($A1, " ", REPT(" ",99)),MAX((COLUMNS($A:A)-1)*99,1),99))

and fill right as far as required.

Of course, if this does not need to be dynamic, as @pnuts commented, merely use text-to-columns with space as the delimiter.

Upvotes: 2

Related Questions