|Let me start by saying that you should try all of this in a backup copy of your workbook since macros can not be undone.|
You didn't include Column Letters or Row Numbers with your example data, so the code below assumes the range begins in A1. The code also assume that your data is in Sheet 1. The code would need to be modified if those assumptions are incorrect.
I have included 2 macros in this post. The first macro, ReBuildSheet1 will allow you to easily rebuild Sheet 1 after you run the Split_E macro. I'll explain why you might want to do that a little later.
In order for the ReBuildSheet1 to work, you must first copy Sheet1 to Sheet2.
1 - Right-Click the sheet tab for Sheet 1
2 - Choose "Move or Copy"
3 - Click the Sheet 2 entry in the "Before Sheet" list
4 - Check the "Create a copy" box
5 - Click OK
That will create a duplicate copy of Sheet 1 which you can use to rebuild Sheet 1 by running the following macro
'Copy Sheet 2 to Sheet 1
lastRw = .Cells(.Rows.Count, "A").End(xlUp).Row
lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Range(.Cells(1, 1), .Cells(lastRw, lastCol)).Copy _
OK, based on your example data, I believe the following code does what you asked for with one minor exception. For the time being, instead of replacing the data in Column E with the sub-strings, it places those sub-strings in Column H. There are 2 reasons for that:
1 - The code retains the current strings in Column E to use them to determine which sub-string to place in Column H.
2 - It allows you to review the results to make sure the code is splitting the strings in the manner you want.
In the code, I have included a section that will replace Column E with Column H, but it is currently Commented out so it will not do the replacement. If you are happy with what you see in Column H, then you can run the ReBuildSheet1 macro to get Sheet 1 back to its original layout, then un-comment the 2 instructions at the end of the Split_E code and run it again. This time the "new data" in Column H will be Cut/Pasted into Column E.
Note: If Column H already contains data, you can either change the reference to Column H in the code to another Column letter or insert a Column H for the code to use.
In addition, since you mentioned that you are "new to VBA" you might want to review this How To. It may help you understand how the 2 macros in this post do what they do.
Let me know if you have any questions.
'Determine last cell with data in Column E
lastRw = .Cells(Rows.Count, "E").End(xlUp).Row
'Loop through Column E
For SrcRw = lastRw To 2 Step -1
'Count number of spaces in Column E cells
numSpace = UBound(Split(Cells(SrcRw, "E"), " "))
'Copy/Insert Rows based on Number of spaces
For newRw = 1 To numSpace
'Set variable for Split Array element
newData = (numSpace - newRw + 1)
'Place correct Split Array element in Column H
.Cells(SrcRw, "H") = Split(Cells(SrcRw, "E"), " ")(newData)
.Cells(SrcRw, "E").EntireRow.Insert shift:=xlDown
'Fix new data in last Copied cell
.Cells(SrcRw, "H") = Split(Cells(SrcRw, "E"), " ")(0)
'Determine last cell with data in Column H, Replace Column E
'**** Uncomment the next 2 lines to replace Column E data
'lastH_Rw = .Cells(Rows.Count, "H").End(xlUp).Row
'.Range("H2:H" & lastH_Rw).Cut .Range("$E$2")
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.