Search for data in a specific format

[Gismo]

Thank you everyone for the assistance
this was a great help

 

[Minty]

Oh, got it, thank you very much

Expr1: Replace([Documentation],Chr(10),Chr(13) & "; " & Chr(10))

View attachment 86184

Actually, you don't need half that if all you want is to separate the strings with the semicolon simply use

Replace([Documentation],Chr(10), "; " )

 

[Gasman]

No

Oh, got it, thank you very much

Expr1: Replace([Documentation],Chr(10),Chr(13) & "; " & Chr(10))

View attachment 86184

No you replace 10 with 13 and 10 and possible a semi colon, not the other way around?

 

[MajP]

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.

65-11-00, 6-1165-11-00, 6-1365-11-00, 6-1465-21-00, 6-20

You could use regular expressions to search for the pattern
##-##-## and replace with a ; ##-##-##

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

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]

Trouble is @MajP, that is not the pattern
65-11-00,6-11 is the pattern, but could be 65-11-00, 6-1

Or at least that was my understanding?

That does come close though.

 

[MajP]

@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.

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.

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]

@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.

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

Ah I see, nicely done.

 

[jdraw]

I haven't seen a response from Gismo since post #21 ---???

 

[Minty]

I haven't seen a response from Gismo since post #21 ---???

Maybe we scared him/her off?
It is nearly Halloween...

 

[MajP]

I haven't seen a response from Gismo since post #21 ---???

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

 

[Gismo]

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.

Thank you

you were a great help
yes post 21 was my thank you post