I do not know of a formula that will do this, but try this macro.
Code:
Sub test()
Dim x As Variant
Dim y As String
Dim j As Long: j = 0
Dim LR As Long
Dim i As Long
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
With Cells(i, 1)
x = Split(.Value, " ")
LR = Range("A" & Rows.Count).End(xlUp).Row
If Cells(5, 1) = "" Then
Else
j = j + 1
End If
With .Offset(LR - j).Resize(UBound(x) + 1)
.Value = Application.Transpose(x)
End With
End With
Next i
For i = 5 To Range("A" & Rows.Count).End(xlUp).Row
Cells(i, 1).Value = "'" & Cells(i, 1).Value
Next i
End Sub
Paste code in a Normal module - Where to paste code
- Highlight macro to copy >> Ctrl + C >> Open your workbook
- Alt + F11 >> opens the Visual Basic Editor (VBE)
- Ctrl + R >>opens the Project Explorer (if not already open on left side of screen)
- Insert menu >> Module or Alt + I, M >> activates the Insert menu and inserts a Standard Module
- Paste code >> Ctrl + V (right side of screen)
- Alt + Q >> exits VBE and returns to Excel
- Back in Excel >> Alt + F8 >> Macro Dialog Box >> Highlight macro >> Run
Since you did not post a sample other than in post #1, my thought is the sample covers the first four rows of column A