I'm looking for a macro that could split text based on character "-". I have something like product ID made from many pieces separeted with "-". Those pieces have not always the same length but I always have the same number of them (always have four "-").
This ID is in one cell and it looks like this:
02-aaaa-mbd-98-2a.
As an output I want to have 02, aaaa, mbd, 98 and 2a in separeted cells
You can use the following:
Function EXTRACTELEMENT(Txt As String, n, Separator As String) As String
On Error GoTo ErrHandler:
EXTRACTELEMENT = Split(Application.Trim(Mid(Txt, 1)), Separator)(n - 1)
Exit Function
ErrHandler:
' error handling code
MsgBox "Error, verify the input data."
EXTRACTELEMENT = CVErr(xlErrNA)
On Error GoTo 0
End Function
When cell A1 has the desired input, you use the function =EXTRACTELEMENT($A$1;1;"-") on cell B1 and =EXTRACTELEMENT($A$1;2;"-") on cell C1 and so on.
The result:
The Regex101 and the code for values on column A:
Dim str As String
Dim objMatches As Object
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow
str = Cells(i, "A")
Set objRegExp = CreateObject("VBScript.RegExp") 'New regexp
objRegExp.Pattern = "[\d\w]+?(?=\-|$)"
objRegExp.Global = True
Set objMatches = objRegExp.Execute(str)
If objMatches.Count <> 0 Then
k = 2
For Each m In objMatches
Cells(i, k) = m.Value
k = k + 1
Next
End If
Next i
The result is the same image as using an UDF.
Remember to enable the reference
