CODEWITHSUNDEEP
vbastringms-access
AccessOPS
AccessOPS

Reputation: 11

VBA Split a String in two parts

I'm trying to obtain two codes from this string: "HL PNX-70[15200]" But with this code, I obtain two times the same output: "HL PNX-70". So, the code is not properly done. How to obtain the output '15200' from the above mentioned String?

Code:

Private Sub Comando221_Click()
MsgBox (Right(Split("HL PNX-70[15200]", "[")(0), 50))
MsgBox (Left(Split("HL PNX-70[15200]", "[")(0), 50))
End Sub

Upvotes: 1

Views: 1190

Answers (4)

Santosh
Santosh

Reputation: 12353

Are you looking for this ?

 Sub Test()

      MsgBox Split("HL PNX-70[15200]", "[")(0)
      MsgBox Replace(Split("HL PNX-70[15200]", "[")(1), "]", "")

 End Sub

enter image description here

Upvotes: 2

Peter Ksenak
Peter Ksenak

Reputation: 315

Here is your code

 Dim text, text1, text2 As String
 text = "HL PNX-70[15200]"
 text1 = Break_String(CStr(text), 0)
 text2 = Break_String1(Break_String(CStr(text), 1))

Function Break_String(a As String, pos As Integer) As String

Dim WrdArray() As String
WrdArray() = Split(a, "[")
Break_String = WrdArray(pos)

End Function

Function Break_String1(a As String) As String

Dim WrdArray() As String
WrdArray() = Split(a, "]")
Break_String1 = WrdArray(0)

End Function

Upvotes: 0

Error 1004
Error 1004

Reputation: 8220

You could try:

    Option Explicit

    Sub Test()

        Dim str As String, Result As String
        Dim Start_Point As Long, No_Characters As Long

        str = "HL PNX-70[15200]"

        Start_Point = InStr(str, "[") + 1
        No_Characters = Len(str) - Start_Point

        Result = Mid(str, Start_Point, No_Characters)
        Debug.Print Result

    End Sub

Upvotes: 0

SJR
SJR

Reputation: 23081

Split returns a zero-based array so you are interested in the second element, index 1. Both lines of your code are extracting "HL PNX-70" and the leftmost and rightmost 50 characters will clearly be the same.

This code illustrates two ways of extracting the desired string for your specific example, but it is not necessarily ironclad if you are working with multiple different types of string. You could also use Instr, as per the other answer, or look at regular expressions if you need more complex pattern matching.

Sub y()

Dim s As String, v

s = "HL PNX-70[15200]"
v = Split(s, "[")
Debug.Print v(0) 'HL PNX-70
Debug.Print v(1) '15200]

MsgBox Left(v(1), Len(v(1)) - 1) '15200

v = Split(v(1), "]")
MsgBox v(0) '15200

End Sub

Upvotes: 0

Related Questions