Is there a function to split a string

[mtheriault2000]

Hello

I taught that i did work with a function to split a delimited string. If i remember it was like

ReferenceString = "Eur.Usd, 3 Minutes, 21.03"

Cell A1 = Splitfunction (referencestring, ",",1) would equal "Eur.Usd"
Cell A2 = Splitfunction (referencestring, ",",2) would equal "3 Minutes"
Cell A3 = Splitfunction (referencestring, ",",2) would equal ""21:03"

Is there a splitfunction like that? I just can't find it.... Maybe I dream it

Martin

 

[Marcelo Branco]

Hi,

Maybe this UDF

Function textSpl(t As String, pos As Long, Optional sep As String = ",") As String
    Dim s As Variant
 
    s = Split(t, sep)
    If pos > UBound(s) + 1 Then MsgBox "Wrong position": Exit Function
    textSpl = Trim(s(pos - 1))
End Function

Assuming your reference string in B1
Usage
A1
=textSpl($B$1,ROWS($A$1:A1))
copy down

HTH

M.

 

[mtheriault2000]

Thanks, I will take a look at your code


By reading in Office documentation, i Foud the split command
I'm working on a split string, but the function does not work yet

Public Function SplitString(ByVal Text0 As String, ByVal Pos As Long) As String

    Dim Areas1(20) As Areas
    Dim Text1 As String
    Text1 = Text0
    Areas1(Pos) = Split(Text0, ",")
    SplitString = Areas1(Pos)
End Function

Martin

 

[mtheriault2000]

I did create a function to split my string

To be call with a string and long number representing the position of the targeted word

EX:
SplitString( "Hi, this, is a,Function,To split,a String"),5) will return "To Split"

SplitString("12345,98765,43210,8520",2) = "98765"

Public Function SplitString(ByVal Text1 As String, WordPos As Long) As String
    Dim CommaPos As Long
    Dim LeftBorder, RightBorder As Long
    Dim WordCounter As Long
    Dim tempstring As String
    Dim TestString As String
    Dim pos As Long
    
    pos = WordPos
    CommaPos = 0
    WordCounter = pos
    
    'LeftBorder
    If InStr(1, Text1, ",") <> 0 Then
        While WordCounter - 1 <> 0
            CommaPos = InStr(CommaPos + 1, Text1, ",")
            pos = pos - 1
            WordCounter = WordCounter - 1
        Wend
        LeftBorder = CommaPos + 1
        If InStr(LeftBorder + 1, Text1, ",") <> 0 Then
            RightBorder = InStr(LeftBorder + 1, Text1, ",")
        Else
            RightBorder = 9999
        End If
        
    Else
        LeftBorder = 1
        RightBorder = 9999
    End If
    
    SplitString = Mid(Text1, LeftBorder, (RightBorder - LeftBorder))
  
End Function

 

[RoryA]

Public Function SplitString(ByVal Text0 As String, ByVal Pos As Long) As String
    SplitString = Split(Text0, ",")(Pos - 1)
End Function