Function to split a string

[camle]

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

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

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.
Also to be able to add additional info in the array.

Thanks

 

[AlphaFrog]

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?

 

[camle]

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

 

[Rick Rothstein]

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

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

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.
Also to be able to add additional info in the array.

Give this function a try...

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

 

[AlphaFrog]

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

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))

 

[Rick Rothstein]

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

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.

 

[AlphaFrog]

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

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

 

[camle]

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