CODEWITHSUNDEEP
vbaexcel
MikoSh
MikoSh

Reputation: 55

Text spliting based on special character in Excel

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

Upvotes: 0

Views: 332

Answers (3)

danieltakeshi
danieltakeshi

Reputation: 939

You can use the following:

Split Function as an UDF

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:

Result 1

Regex

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

Upvotes: 1

amitklein
amitklein

Reputation: 1395

Split("02-aaaa-mbd-98-2a", "-") //will five you {02,aaaa,mbd,98,2a}

more information here

Upvotes: 0

Maldred
Maldred

Reputation: 1104

Check out link for help with splitting text-to-columns, if you want a macro for this, simply use "Record Macro"

Upvotes: 0

Related Questions