CODEWITHSUNDEEP
vbaexcelcsv
drylkuch
drylkuch

Reputation: 65

Excel TextToColumns with two Custom Delimiters

I am trying to use VBA to separate text in a cell using both hyphens and forward slashes as delimiters. Using one or the other is fairly simple. However, neither of those characters are standard delimiters in Excel and I can't figure out how to use two custom delimiters.

I am aware that Excel has a text-to-column feature that is much easier to use and doesn't require me to write any code, but this is part of a larger project that requires it to be in VBA.

Here is the code that I have.

Dim wireIDCell As Range
Set wireIDCell = Range("A1")

wireIDCell.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
    Other:=True, OtherChar:="/"

Attempting to do some kind of magic with string functions was going to be my next idea, but I wanted to know if someone out there has any better ideas first

Upvotes: 1

Views: 9337

Answers (1)

skkakkar
skkakkar

Reputation: 2828

Following @Jeeped advice to limit to single delimiter , I used SUBSTITUTE Function to replace hyphen with forward slash for all instances. Finally added VBA code for Text-To-Column as per Macro recorder i.e. not simplified. It is because I want flexibility with OP to keep provision for more instances of delimiters. Pl. Try This:

 Sub Text_to_col()
    Dim rng As Range
    Dim r As Range
    Set rng = ActiveSheet.UsedRange
    For Each r In rng
        r.Value = Application.WorksheetFunction.Substitute(r.Value, "-", "/")
    Next r

    With ActiveSheet.UsedRange.Columns("A").Cells
        .TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
        TrailingMinusNumbers:=True
    End With
End Sub


   [![snapshot of sample data and results][1]][1]

enter image description here

Upvotes: 2

Related Questions