Text in the middle

[Torstensson]

Hi,

I've got som texts that look like this: Text1>Text2>Text3>Text4>Text5
Is it possible to seperate Text3 with a custom function?
All texts have varible lenghts but the seperator is always the same.

I've used both a sub procedure and text to columns but a function would be best case in this document.

Been trying but can't figure it out.

/Mikael

 

[njimack]

=mid(a1,find("#",substitute(a1,">","#",2))+1,find("~",substitute(a1,">","~",3))-find("#",substitute(a1,">","#",2))-1)

 

[jim may]

I need to know more (than what you are offering) to help.

 

[kpark91]

Ah njimack found a better solution.
I just used a very... unelegant method as I didn't know substitute function so thanks njimack!

=MID(A22,FIND(">",A22,FIND(">",A22)+1)+1,FIND(">",A22,FIND(">",A22,FIND(">",A22)+1)+1)-FIND(">",A22,FIND(">",A22)+1)-1)

 

[mikerickson]

This UDF should do it for you.

Function SubString(N As Long, BigString As String, Delimiter As String) As String
    If N < 0 Then SubString = vbNullString:Exit Function
    If Ubound(Split(BigString, Delimiter)) < N Then SubString = vbNullString:Exit Function

    SubString = Split(BigString, Delimiter)(N)
End Function

Note that this is a 0-based system,

=SubString(1, "Text1>Text2>Text3>Text4>Text5", ">") returns "Text2"

=SubString(2, "Text1>Text2>Text3>Text4>Text5", ">") returns "Text3"

 

[Jonmo1]

Welcome to the board...

Try
=TRIM(MID(SUBSTITUTE(A1,">",REPT(" ",LEN(A1))),LEN(A1)*2,LEN(A1)))

The > is your seperator
The 2 is the Nth-1 value you want to find.

Hope that helps.

 

[Torstensson]

Thanks a lot guys, works like a charm!
I gotta say you really reply fast, thanks a bunch!

 

[villy]

=MID(C37,SEARCH("%",SUBSTITUTE(C37,">","%",2))+1,LEN(C37)-SUM(SEARCH("%",SUBSTITUTE(C37,">","%",2)),LEN(MID(C37,SEARCH("%",SUBSTITUTE(C37,">","%",3)),LEN(C37)-LEN(LEFT(C37,SEARCH("%",SUBSTITUTE(C37,">","%",3))-1))))))

Try this quite long...

 

[Rick Rothstein]

Here is a fairly flexible function that can be used either from your own VB code or as a UDF (user defined function) on a worksheet...

Function GetField(ByVal TextIn As String, _
                  ByVal Delimiter As String, _
                  ByVal FieldNumber As Long, _
                  Optional FromFront As Boolean = True) As Variant
  Dim Fields() As String
  Fields = Split(TextIn, Delimiter)
  If FromFront Then
    GetField = Fields(FieldNumber - 1)
  Else
    GetField = Fields(UBound(Fields) - FieldNumber + 1)
  End If
End Function

Note that the optional argument allows you to specify the field's count number from either the front (the default) or from the back of the text.

Okay, so to use this from a worksheet, your formula would look like this...

=GetField(A1,">",3)

or to use it within your own VB code, it could look something like this...

TextToParse = "Text1>Text2>Text3>Text4>Text5"
DelimiterCharacter = ">"
WhichField = 3
MsgBox GetField(TextToParse, DelimiterCharacter, WhichField)