splitting text

[grapevine]

I have text of varying length that I need to split up into three fields. I am using a vba code (kindly shown to me on a previous post) to split the first word, but in this particular macro I need to remove the last word. Can anyone help with this. This is the code that I have at present which is displaying the first word.

Sub splitDescription()
Dim c As Range, t
For Each c In Range("e2:e" & Range("e" & Rows.Count).End(xlUp).Row)
t = Split(c)

c.Offset(, 1) = t(0)

Next
End Sub

The data I am trying to split consists of descriptions of varying lengths and I need to remove the first word and the last word and keep the middle text. Below is an example

SUEDE ROUCHED TRIM COURT GOLD
LEATHER/PATEN METALLIC SNAKE COURT BLACK
SUEDE RIBBONED BOW PURPLE

If anyone can help me out I would be most grateful.

Many thanks

 

[jindon]

Suppose txt = "SUEDE ROUCHED TRIM COURT GOLD"

x = Split(txt)

array x is created from the Split function devided by space
x(0) = "SUEDO"
x(1) = "ROUCHED"
x(2) = "TRIM"
x(3) = "COURT"
x(4) = "GOLD"

myMaterial = x(0) : x(0) = ""

Store 1st word "SUEDO", x(0) in variable myMaterial
then empty x(0), now x(0) = ""

myColor = x(UBound(x)) : x(UBound(x)) = ""

UBound function returns the upper bound of an array, so
x(UBound(x)) = x(4) = "GOLD"
then store the info into variable myColor, and empty the element.

myDesc = Join(x, " ")

Join function joins eall the lements in 1D array together with the string specified by 2nd arguments.
(" " space in this case)
Now, both x(0), the first word, and the last x(UBound(x)) are empty.
When joined, it should be
"" ROUCHED TRIM COURT ""

grapevine = Array(myMaterial, myDesc, myColor)

Return the result as an array, so that it will show in the differect cells.

Hope this helps

 

[grapevine]

Thank you for your very detailed description, I will study this in detail later as I am just off to work now. Thank you so so much for all your help and advice, it really is appreciated.

Best wishes

Grapevine