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

 

[grapevine]

HOTPEPPER,

Thank you for your explanation. This file is sent in as a text file. I can seperate all the other fields into 10 different columns but was having difficulty with this particular text as needs to be split into three fields, material, description and colour and all the elements are of varying lengths.

I have used split(0) to obtain the material
I have used the code that you supplied to seperate the description
I just need to obtain the colour to complete the reformatting.

I hope this explains more clearly what I am trying to achieve.

Many thanks for your help.

 

[jindon]

does this help?

select 3 cells like b1:d1 and type
=grapevine(A1)
then confirm with Ctrl+Shift+Enter

Function grapevine(txt As String)
Dim myMaterial As String, myDesc As String, myColor As String, x()
x = Split(txt)
myMaterial = x(0) : x(0) = ""
myColor = x(UBound(x)) : ReDim Preserve x(UBound(x)-1)
myDesc = Join(x, " ")
grapevine = Array(myMaterial, myDesc, myColor)
End Function

 

[grapevine]

Thank you for your code, I am sure it will be much quicker, but the result I am getting at present is #value in all three cells and the formula appearing in the formula bar is {=grapevine(a1)}.

ps: I do not need to call these from the spreadsheet as it will be called as a function within other code. I dont know if this will make it easier

Thank you for your help so far

 

[Scott Huish]

If color is always the last element, you can retrieve that from the split like this, for example:

Msgbox t(ubound(t))

 

[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 so much for your patience, it really is appreciated.

The sub routine is correctly calling the function and finding the correct text to split as when I hover my mouse over the first line of the function below

Function grapevine(txt As String)

the function can see the first line of the text that needs to be split, txt="SUEDE ROUCHED TRIM COURT GOLD"
so this is working fine

The line that is causing problems is
x = Split(txt)
I am getting run-time error='13' Type mismatch

Can you please help me once more.

Many thanks

 

[jindon]

OOps
Can you change x() to x in the Dim statement?

 

[grapevine]

Thank you so so much for all your time and patience. It is working perfectly.

Can I ask what the : does in the statements so that I can try and understand this better.

I really am so grateful for all your help

 

[jindon]

":" means another line

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

equals

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

 

[grapevine]

Is x(0) getting the first text?
Why does it need two expressions, what is the x(0) ="" doing?

Many thanks