How text to columns using "-" and "_" at the same time

[Paul21]

Hello there,

I want to split file name in excel using Text to Column vba using dash and underscore as delimeters.
I am using below macro specifying dash or undersocre as OtherChar. How can we have dash and underscore simultaniuosly ?

I want to split this A-B_C into A B C

Thx

Sub text_to_col()

Application.DisplayAlerts = False

Range("A2", Range("A2").End(xlDown)).TextToColumns _
      Destination:=Range("B2"), _
      DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=True, _
      Tab:=True, _
      Semicolon:=False, _
      Comma:=False, _
      Space:=True, _
      Other:=True, _
      OtherChar:="_", _
      
      
Application.DisplayAlerts = True
End Sub

 

[diddi]

Sub text_to_col()
    Application.DisplayAlerts = False
    For Each cell In Range("A2", Range("A2").End(xlDown))
        cell.Value = Replace(cell.Value, "_", "-")
    Next cell
    Range("A2", Range("A2").End(xlDown)).TextToColumns _
          Destination:=Range("B2"), _
          DataType:=xlDelimited, _
          TextQualifier:=xlDoubleQuote, _
          ConsecutiveDelimiter:=True, _
          Tab:=True, _
          Semicolon:=False, _
          Comma:=False, _
          Space:=True, _
          Other:=True, _
          OtherChar:="-"
    Application.DisplayAlerts = True
End Sub

 

[Paul21]

Sub text_to_col()
    Application.DisplayAlerts = False
    For Each cell In Range("A2", Range("A2").End(xlDown))
        cell.Value = Replace(cell.Value, "_", "-")
    Next cell
    Range("A2", Range("A2").End(xlDown)).TextToColumns _
          Destination:=Range("B2"), _
          DataType:=xlDelimited, _
          TextQualifier:=xlDoubleQuote, _
          ConsecutiveDelimiter:=True, _
          Tab:=True, _
          Semicolon:=False, _
          Comma:=False, _
          Space:=True, _
          Other:=True, _
          OtherChar:="-"
    Application.DisplayAlerts = True
End Sub

Hi Didi, works like charm, moreover I do undrstand your solution. Appreciate!!!

 

[diddi]

thanks for that. you can tick "mark as solution" if it has sorted the problem