vba macro splitting string/text

[Kaniguan1969]

Hi

I have a requirements to split a string in vba macro. May i ask your help on how to do this in vba macro. thanks.

sample data: description will be the one to perform splitting.


description-------------result 1------------------result2
---------------------------------------------------------
SP3004430X8SIL --------300-4430-X8SIL------------X8SIL
RALP3004430K8SIL--------300-4430-K8SIL-----------K8SIL
RALP3000425JPH4S16WHT---300-0425-JPH4S16WHT------JPH4S16WHT
SP2452425EXHBTWHTGSM----245-2425-EXHBTWHTGSM-----EXHBTWHTGSM
RALP3001710M766---------300-1710-M766 ----------M766
DR3004410ONE------------300-4410-ONE--------------ONE

[westconn1]

you can test

a = Split(s, vbNewLine)
For i = 0 To UBound(a) - 1
Do
strt = InStr(a(i), "--")
If strt = 0 Then Exit Do
For c = strt + 2 To Len(a(i))
If Not Mid(a(i), c, 1) = "-" Then
nd = c
Exit For
End If
Next
a(i) = Left(a(i), strt - 1) & "~" & Mid(a(i), nd )
Loop
l = Split(a(i), "~")
For q = 0 To UBound(l)
Debug.Print l(q), ;
Next
Debug.Print
Nextsee if this can produce the result you desire, where s is the string as posted above

[pike]

possibly adapt a regexp option

Option Explicit
Sub Split_text()
Dim rng As Range
Dim Myarray
For Each rng In Range("A1:A8")
Myarray = Split(Match_Replace(rng), "~")
rng.Offset(, 1).Resize(, UBound(Myarray) + 1) = Myarray
Next rng
End Sub
Function Match_Replace(rng As Range)
Dim Regex As Object
Match_Replace = rng.Text
Set Regex = CreateObject("vbscript.regexp")
With Regex
.Global = True
.Pattern = "(-{2,})"
Match_Replace = Regex.Replace(Match_Replace, String("$1", "~"))
End With
Set Regex = Nothing
End Function

[Kaniguan1969]

Thank you guys for the reply.

Hi pike, got an error, pointing to this code "Match_Replace = Regex.Replace(Match_Replace, String("$1", "~"))". it say type mismatch.

Hi westconn1, got an error, what is the variable data type a,s,i

btw, what is this char ~?

[Kenneth Hobs]

I suspect that you error with pike's solution was that you did not copy the string into cells A1:A8. When you don't attach an example workbook, we have to make assumptions.

For westconn1's solution, comment out Option Explicit. It will need some other iterative steps possibly. It just depends on if it is all one string or one string per cell as pike assumed.

[westconn1]

a and l would be string arrays (or variant)
strt, nd, c and q would be integer (or variant)
s would be a string (or variant)

Dim a as string(), l as string(), strt as integer, nd as integer, c as integer, q as integer, s as string


It just depends on if it is all one string or one string per cell as pike assumed.
i assumed the initial string would come from reading some sort of text file, though this was not specified


btw, what is this char ~?the tilde (chr(126)) character is often used as a delimiter as it seldom found in normal text strings, any other character (eg vbtab) can be used, as long as it is unlikely to be found within the original string

[Kaniguan1969]

Herewith is a sample data as reference.

[Kenneth Hobs]

The two comments show how to get the two results that you asked for.

Function RemovePrefixChars(aStr As String) As String

While Not (IsNumeric(Left(aStr, 1)))
aStr = Right(aStr, Len(aStr) - 1)
Wend
RemovePrefixChars = aStr
End Function

'=Format3nd4ndChars(RemovePrefixChars(B3))
Function Format3nd4ndChars(aStr As String) As String
Select Case True
Case Len(aStr) > 7
Format3nd4ndChars = Left(aStr, 3) & "-" & Mid(aStr, 4, 4) & "-" & Right(aStr, Len(aStr) - 4)
Case Else
End Select
End Function

'=lastfield(E2)
Function LastField(aStr As String, Optional sDelimit As String = "-") As String
Dim a() As String
a() = Split(aStr, sDelimit)
LastField = a(UBound(a))
End Function