How to match accented characters but not tab

Question

I'm trying to match the company name in this string delimited with tabs.
Below table does not have tabs when you copy it, but I have replaced tabs with two spaces, which I assume will work fine for testing.

1025164  HERBEX IBERIA, S.L.U.  KY01  4600292091    
1016379  DRISCOLL´S OF EUROPE B.V.  KY01  4600322589    
1008809  LANDGARD NORD OBST & GEMÜSE GM  KY01  4600347315   
1008835  C.A.S.I. : COOPERATIVA PROVINC  KY01  4600348112   
1019258  SYDGRÖNT EKONOMISK FÖRENING  KY02  4600343422  

(The second column of the above, between 7 digit number and KY0 above)
In real life the columns are not always in the same order since it's a user preference.

I just took a few examples but names could also include /éèáà()´, pretty much anything (sadly).

I found another question here Concrete Javascript Regex for Accented Characters (Diacritics)
When I use the regex patterns in that thread, example: "\t([A-zÀ-ÿ0-9\s\.\,\_\-\'\&]+)\t" (I know some characters are still missing) to match between two tabs it becomes greedy and matches the whole line.

Is there any pattern that could match any character in a company name between tabs (or two spaces as the example above)?

Answer 1

Instead of returning a matched part, I matched everything and replaced it with the 1st capture group. Hope it helps.

Sub Test()

Dim str As String: str = "1025164" & vbTab & "HERBEX IBERIA, S.L.U." & vbTab & "KY01" & vbTab & "4600292091"

With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "(?:^|\t)(?:\d+|KY\d+|([^\t]+))(?=\t|$)"
    Debug.Print .Replace(str, "$1")
End With

End Sub

Have a look at this online demo to test the pattern:

• (?:^|\t) - Match either start line anchor or a tab. Unfortunately the VBA-regex object does not support lookbehinds.
• (?: - Open a non-capture group to start matching all parts you don't want to capture first:
  • \d+ - match 1+ digits;
  • | - Or:
  • KY\d+ - Match "KY" followed by 1+ digits;
  • | - Or:
  • ([^\t]+) - nest a capture group to capture 1+ non-tabs.
  • ) - Close non-capture group.
• (?=\t|$) - Positive lookahead to assert captured text is followed by either a tab or end-line anchor.

Answer 2

I would use a different attempt using the split-command. The following code assumes that you have Tabs as separator and that the company name is found if the column is not numeric (only digits) and does not start with 'KY'.

Function getCompanyName(line As String) As String
    Const separator = vbTab ' Replace with "  " if you need that.
    
    Dim tokens() As String, i As Integer
    tokens = Split(line, separator)
    For i = 0 To UBound(tokens)
        If Not IsNumeric(tokens(i)) And Left(tokens(i) <> "KY") Then
            getCompanyName = tokens(i)
            Exit Function
        End If
    Next
End Function