CODEWITHSUNDEEP
arraysvbaexcel
prf
prf

Reputation: 47

split string with vertical tab as delimiter using vba

How can I split a string in vba with vertical tab as delimiter. 

strWriters = Mid("Richenn M.(there a delimiter here)QA'er:", 9, Len("Richenn M.(there a delimiter here)QA'er:"))
LArray = VBA.Split(Selection.Text, Chr(103))
strWriter = LArray(0)
MsgBox LArray(0)

i tried to searched for the char equivalent but it returns an error for char(103)

It says type mismatch.

Upvotes: 1

Views: 2667

Answers (1)

user4039065
user4039065

Reputation:

The vertical tab character is ASCII &H0B hex or 011 dec¹. VBA uses the Chr and Asc functions while the worksheet uses the CHAR and CODE functions.

Your syntax appears largely correct but there is some confusion as to what you are actually working on. You start with a string constant and string variable (e.g. strWriters) then immediately discard those and work with Selection.Text.

Sub how2split()
    Dim str As String, strWriters As String, a As Long, LArray As Variant

    str = "Richenn M." & Chr(&HB) & "QA'er:"
    'alternate
    'str = Join(Array("Richenn M.", "QA'er:"), Chr(&HB))

    Debug.Print str

    LArray = Split(str, Chr(11))

    For a = LBound(LArray) To UBound(LArray)
        Debug.Print LArray(a) & " is at position " & a
    Next a
End Sub

The Debug.Print command send output to the VBE's Immediate window.

¹ ASCII 103 is a lower-case G (e.g. Chr(103) produces a g).

Upvotes: 2

Related Questions