Find a suitable delimiter

Tested in Excel 2016 (16.0.9330.2073) 64-bit

A function to test which character can be used to join data safely to be able to split it later.

 
 
  1. Public Function FindDelimiter(RangeToTest As Range) As String
  2.     Const DelimiterList = "; , | ! @ # $ % ^ & < > : \ } { +"
  3.     Dim DelimArr, myDelim, Found As Boolean
  4.     Dim i, j
  5.    
  6.     DelimArr = Split(DelimiterList, " ")
  7.    
  8.     For i = 0 To UBound(DelimArr)
  9.         Found = True    'assume the delimiter is ok
  10.        For j = 1 To RangeToTest.Count
  11.             If InStr(1, RangeToTest(j).Value, DelimArr(i)) > 0 _
  12.                 Then Found = False ' if it exists in the analized range, it's not found
  13.        Next j
  14.         If Found Then ' use this one and stop
  15.            myDelim = DelimArr(i)
  16.             Exit For
  17.         End If
  18.     Next i
  19.    
  20.     FindDelimiter = myDelim
  21. End Function

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.