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]

UDF
1) hit Alt + F11
2) go to [Insert] - [Module], paste the code onto the right pane
3) hit Alt + F11

Use in cell like
=grapevine(A1)

Function grapevine(txt As String) As String
With CreateObject("VBScript.RegExp")
     .Pattern = "(^.+\s)|(\b.+$)"
     .Global = True
     grapevine = Trim(.replace(txt,""))
End with
End Function

 

[grapevine]

Thank you for your code. I have followed your instructions, but all I am seeing (not seeing) in the cell where I have typed =grapevine(a1) is blank spaces as I am not getting any error messages. Can you help me any further?

Many thanks

grapevine

 

[jindon]

Can you change the code

Function grapevine(txt As String) As String
With CreateObject("VBScript.RegExp")
     .Pattern = "^\S+"
     txt = Trim(.replace(txt,""))
     .Pattern = "\S+$"
     grapevine = Trim(.replace(txt,""))
End with
End Function

 

[Scott Huish]

Another possibility:

Public Function SplitDescription(r As String) As String
Dim t
t = Split(r)
t(0) = "": t(UBound(t)) = ""
SplitDescription = Trim(Join(t))
End Function

 

[grapevine]

Many thanks for your prompt reply, unfortunately I am still not getting text showing.

Any further suggestions gratefully received

 

[Scott Huish]

Is this not what you wanted?

Excel Workbook
	A	B
1	SUEDE ROUCHED TRIM COURT GOLD	ROUCHED TRIM COURT
2	LEATHER/PATEN METALLIC SNAKE COURT BLACK	METALLIC SNAKE COURT
3	SUEDE RIBBONED BOW PURPLE	RIBBONED BOW
Sheet

 

[jindon]

Use HOTPEPPERS's, nice and easy one

 

[grapevine]

Thank you very much HOTPEPPER, your code is exactly what I need. I think our messages crossed as I had not seen your code when I posted my message saying I was still seeing blanks.

Can I ask for an explanation of what your code is doing so that I can try and extract the colour description from the end of the string. Or is there an easier way of splitting all three elements in one function? (material, description and colour)

I also need to call the function from within a macro which I have written which has already reformatted the rest of the spreadsheet. Would I need to change much? As you can gather, I am a newbie, but going through a steep learning curve at the moment.

Much obliged for your help so far.

 

[Scott Huish]

Split will break apart a string into an array based on a specified delimiter. If no delimiter is specified, space is used.

Join joins the array back together with the delimiter put in between the elements of the array. If no delimeter is specified, space is used.

However since you just seem to want to return a specified part of the string, I'm not sure why you need to break out the middle.

The array from Split starts at element 0
0 being the first element, 1 being the second element, etc.

So if you want the first part from the split, you could just return that element

For example:

Public Function test(r As String) As String
Dim t, x As Long
t = Split(r)
For x = 0 To UBound(t)
    MsgBox t(x)
Next x
End Function