# Function to split a string

#### camle

##### Board Regular
Need a little more assistance in creating a function to split up string.

Ex.
Uncle Grandpa (2013) -2015-06-08- Body Trouble

Green is Series name, they can be:
Uncle Grandpa (2013)
3rd Rock from the Sun
227
Bones

Blue is the "parts like" (look in the vba), they can be:
S##E##
S##E##E##
-####-##-##-
#x#
##x##

Blue Code: Works Great
Code:
``````Function Episode(DataLine As String) As String
Dim x As Long, Parts() As String
Parts = Split(DataLine)
For x = 0 To UBound(Parts)
If Parts(x) Like "*#x#*" Or Parts(x) Like "S#*E#*" Or Parts(x) Like "-####-##-##-" Then
Episode = Parts(x)
Exit For
End If
Next
End Function``````

Red Works Great
Code:
``````Function EpisodeName(DataLine As String) As String
Dim x As Long, AfterEpisode As String, Parts() As String
Parts = Split(DataLine)
For x = 0 To UBound(Parts)
If Parts(x) Like "*#x#*" Or Parts(x) Like "S#*E#*" Or Parts(x) Like "*-####-##-##-*" Then
Parts = Split(DataLine, " ", x + 2)
AfterEpisode = Parts(UBound(Parts))
If InStr(AfterEpisode, ".") Then AfterEpisode = Left(AfterEpisode, InStrRev(AfterEpisode, ".") - 1)
EpisodeName = AfterEpisode
Exit For
End If
Next
End Function``````

I would like to have a Function the get the Green Series name.

Thanks

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
I take it that the Green part is not always the first part of the string?

If it is, this this would return the 1st part of the split.

Green = Split(DataLine)(0)

If not, can you provide more detail on the variance and several examples?

Last edited:
I take it that the Green part is not always the first part of the string?

If it is, this this would return the 1st part of the split.

Green = Split(DataLine)(0)

If not, can you provide more detail on the variance and several examples?

It is always the 1st part of the string.
How would I place it in the vba?

Thanks

Need a little more assistance in creating a function to split up string.

Ex.
Uncle Grandpa (2013) -2015-06-08- Body Trouble

Green is Series name, they can be:
Uncle Grandpa (2013)
3rd Rock from the Sun
227
Bones

Blue is the "parts like" (look in the vba), they can be:
S##E##
S##E##E##
-####-##-##-
#x#
##x##

Blue Code: Works Great
Code:
``````Function Episode(DataLine As String) As String
Dim x As Long, Parts() As String
Parts = Split(DataLine)
For x = 0 To UBound(Parts)
If Parts(x) Like "*#x#*" Or Parts(x) Like "S#*E#*" Or Parts(x) Like "-####-##-##-" Then
Episode = Parts(x)
Exit For
End If
Next
End Function``````

Red Works Great
Code:
``````Function EpisodeName(DataLine As String) As String
Dim x As Long, AfterEpisode As String, Parts() As String
Parts = Split(DataLine)
For x = 0 To UBound(Parts)
If Parts(x) Like "*#x#*" Or Parts(x) Like "S#*E#*" Or Parts(x) Like "*-####-##-##-*" Then
Parts = Split(DataLine, " ", x + 2)
AfterEpisode = Parts(UBound(Parts))
If InStr(AfterEpisode, ".") Then AfterEpisode = Left(AfterEpisode, InStrRev(AfterEpisode, ".") - 1)
EpisodeName = AfterEpisode
Exit For
End If
Next
End Function``````

I would like to have a Function the get the Green Series name.
Give this function a try...
Code:
``````Function SeriesName(DataLine As String) As String
Dim x As Long, Parts() As String
Parts = Split(DataLine)
For x = 0 To UBound(Parts)
If Parts(x) Like "*#x#*" Or Parts(x) Like "S#*E#*" Or Parts(x) Like "-####-##-##-" Then
SeriesName = Trim(Left(DataLine, InStr(DataLine, Parts(x)) - 1))
Exit For
End If
Next
End Function``````

This will return parts (separated by space character) by position regardless of content

Code:
``````Function Parts(DataLine As String, Pos As Integer) As String
Parts = Split(DataLine)(Pos - 1)
End Function``````

=PARTS(A1,1) returns the 1st part
=PARTS(A1,2) returns the 2nd part
=PARTS(A1,3) returns the 3rd part

Or this:
1st part
=LEFT(A1,FIND(" ",A1)-1)

2nd part
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100))

3rd part
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),200,100))

This will return parts (separated by space character) by position regardless of content

Code:
``````Function Parts(DataLine As String, Pos As Integer) As String
Parts = Split(DataLine)(Pos - 1)
End Function``````

=PARTS(A1,1) returns the 1st part
=PARTS(A1,2) returns the 2nd part
=PARTS(A1,3) returns the 3rd part

Or this:
1st part
=LEFT(A1,FIND(" ",A1)-1)

2nd part
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),100,100))

3rd part
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),200,100))
The OP wants "Uncle Grandpa (2013)" returned from this...

"Uncle Grandpa (2013) -2015-06-08- Body Trouble"

I don't see how your function does that.

You're right. I wasn't paying attention.

I'll leave it to you since you've already taken over again.

You're right. I wasn't paying attention.

I'll leave it to you since you've already taken over again.

Thank you Both, it works very good

Replies
1
Views
516
Replies
22
Views
754
Replies
1
Views
347
Replies
3
Views
625
Replies
0
Views
353

1,203,171
Messages
6,053,874
Members
444,692
Latest member
Queendom

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back