0

Why doesn't Excel allow us to use two slashes in the Data > Text to Columns > Delimited > Other section?

I have several rows which have similar data. Each row also has two contiguous slashes.

I want to separate the column which begins from the two slashes.

How to do it ?

zeewagon
  • 1,835
  • 4
  • 18
  • 22

2 Answers2

0

Tick the "Treat consecutive delimiters as one", in the other delimiters enter one slash, should resolve your problem

Ricards Porins
  • 384
  • 1
  • 7
  • No. I want to enter two slashes in the other delimiters section. This is not allowing. :( – zeewagon May 02 '18 at 10:03
  • 1
    The only work around if for some reason that does not work is to preform a search and replace, replace two slashes with another delimiter and use said delimiter to preform text to column split. – Ricards Porins May 02 '18 at 10:04
  • If I am not mistaken it is limited to one delimiter. So you would have to replace your two slashes with a new unique delimiter, hope it helps. – Ricards Porins May 02 '18 at 10:04
  • yes, it helped. thanks. But isn't there any straight solution for it ? – zeewagon May 02 '18 at 10:17
  • 1
    Not that I can think of, the delimiters are limited to 1 character, haven't given any through if a macro could resolve this, but I think the replace approach is the most "straight forward" approach, – Ricards Porins May 02 '18 at 10:20
  • If so, Thanks for it :) – zeewagon May 02 '18 at 10:21
0

Text-to-Columns can only accept a single character as the 'other' delimiter but Split can divide on any number of characters as the delimiter¹. Simply write your own version of Text-to-Columns.

Option Explicit

Sub myT2C()
    Dim vals As Variant, tmp As Variant, i As Long, j As Long, mx As Long

    With Worksheets("sheet8")
        vals = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)).Value2
        For i = LBound(vals, 1) To UBound(vals, 1)
            tmp = Split(vals(i, 1), "//")
            mx = Application.Max(mx, UBound(tmp) + 1)
            ReDim Preserve vals(LBound(vals, 1) To UBound(vals, 1), 1 To mx)
            For j = LBound(tmp) To UBound(tmp)
                vals(i, j + 1) = tmp(j)
            Next j
        Next i
        .Cells(2, "A").Resize(UBound(vals, 1), UBound(vals, 2)) = vals
    End With
End Sub

¹ There is probably a limit that I'm unaware of; perhaps 255 characters.