So I missed the whole part of the hidden delimiter. However if anyone is interested how to do this if no delimiter was present you could try Regular expressions. Assume the following.
You could use regular expressions to search for the pattern
##-##-## and replace with a ; ##-##-##
Code:
Public Function AddDelimiter(SearchIn As Variant) As String
'Need Microsoft VBScript Regular Expressions
Dim objRegExp As VBScript_RegExp_55.RegExp
Dim objMatch As VBScript_RegExp_55.Match
Dim ReturnMatches As VBScript_RegExp_55.MatchCollection
Dim i As Integer
Dim Match As String
If Not IsNull(SearchIn) Then
'Create a regular expression object.
Set objRegExp = New RegExp
'Set the pattern by using the Pattern property.
objRegExp.Pattern = "[0-9]{2}-[0-9]{2}-[0-9]{2}"
'Set Case Insensitivity.
objRegExp.ignorecase = True
'Set global applicability. Not sure what that does
objRegExp.Global = True
'Test whether the String can be compared. Not sure what that does
If (objRegExp.test(SearchIn) = True) Then
Set ReturnMatches = objRegExp.Execute(SearchIn) ' Execute search.
'lets assume you only get one match
For i = 0 To ReturnMatches.Count - 1
If Match <> ReturnMatches(i) Then
Match = ReturnMatches(i)
SearchIn = Replace(SearchIn, Match, "; " & Match)
' Debug.Print ReturnMatches(i)
End If
Next i
Else
Debug.Print "Could not compare string"
End If
'strip the leading ;
If Left(SearchIn, 2) = "; " Then SearchIn = Mid(SearchIn, 3)
AddDelimiter = SearchIn
End If
End Function
Testing the above string I get
Code:
Public Sub testit()
Debug.Print AddDelimiter("65-11-00, 6-1165-11-00, 6-1365-11-00, 6-1465-21-00, 6-20")
' 65-11-00, 6-11; 65-11-00, 6-13; 65-11-00, 6-14; 65-21-00, 6-20
End Sub
@Gasman,
I was looking just for the 65-11-00 portion. Anything in the format of ##-##-## would get a semicolon before it. So if the input was different it would still handle that.
Code:
Public Sub testit()
Debug.Print AddDelimiter("65-11-00,6-1165-11-00,6-1365-11-00,6-1465-21-00,6-20")
'65-11-00,6-11; 65-11-00,6-13; 65-11-00,6-14; 65-21-00,6-20
End Sub
l
You could even hand the worst case if there was no comma or spaces.
Code:
Public Sub testit()
Debug.Print AddDelimiter("65-11-006-1165-11-006-1365-11-006-1465-21-006-20")
'65-11-00 6-11; 65-11-00 6-13; 65-11-00 6-14; 65-21-00 6-20
End Sub
@Gasman,
I was looking just for the 65-11-00 portion. Anything in the format of ##-##-## would get a semicolon before it. So if the input was different it would still handle that.
Code:
Public Sub testit()
Debug.Print AddDelimiter("65-11-00,6-1165-11-00,6-1365-11-00,6-1465-21-00,6-20")
'65-11-00,6-11; 65-11-00,6-13; 65-11-00,6-14; 65-21-00,6-20
End Sub
I am pretty sure the OP's specific question was answered before post 21. Everything after that was based on a hypothetical much harder problem, and was presented for other's interest as previously mentioned.
However if anyone is interested how to do this if no delimiter was present
I am pretty sure the OP's specific question was answered before post 21. Everything after that was based on a hypothetical much harder problem, and was presented for other's interest as previously mentioned.