Finding wildcard match from table row (PDF file import)

[dotsent]

Hi guys!

I'm importing PDF-files into Excel in Office365 environment, using PowerQuery's new-ish PDF file support. However, given there are some minor differences over files, PowerQuery / Excel parses data a bit differently, based on individual PDF.

I'm counting on the number of imported rows and data position per row to be constant (2), however the number of imported columns can vary to some extent, which also move the data cell I'm after (as well as change column header name). What I need to do, is grab a certain text from the imported table, but given the fluctuation in columns, I can't hardcode the specific column range, but have to use wildcard search to first pinpoint the data on row and then grab it. Data contents also vary, but can be identified through wildcard.

So, I have a table and its 2nd (list)row is worksheet row 3 and I expect the data to be anywhere in the range of B3:F3 with the contents starting "Mytext*". My goal is to pinpoint the text, and grab the entire cell contents. Any tips on how to do this?

Using a VBA code to loop through all the cells and run search, should work, but there might be a simpler solution via worksheet formula... I can successfully target it with XMATCH, but only getting column number in response). Is there a way to convert this to cell address?

Thanks a lot in advance!

 

[Alex Blakenburg]

To answer your specific question it would look something like this:-
Ideally someone will answer with how they would actually do it. I would probably normally use ".find with xlPart but that will search for anywhere in the cell text, and I don't know if it being at the start of the text is important to you.
Whatever method you use it will most likely need some error handling for if it can't find it.

Sub FindAddress()
    Dim AddressOfCell As String

    AddressOfCell = ""
    
    On Error Resume Next
    AddressOfCell = WorksheetFunction.Index(Range("$A3:$F3"), 0, WorksheetFunction.XMatch("Mytest" & "*", Range("$A3:$F3"), 2)).Address
    
    If AddressOfCell = "" Then
        ' what to do if not found
        MsgBox "Not Found"
    End If
    
    On Error GoTo 0
       
End Sub

 

[dotsent]

Hi Alex, thanks for your response. But would there perhaps be a more straightforward solution via Excel worksheet formulas? I use VBA quite a bit, but sometimes worksheet formulas w/o coding are good enough.

 

[dotsent]

OK, I think now that if I can get column no with XMATCH and I know table starts from column A, I can just use OFFSET from reference cell A3

 

[Alex Blakenburg]

I am a bit confused. If that's all you are trying to do is return the cell value, almost any lookup function will do that. Xlookup would be the simplest.

=XLOOKUP("MyTest"&"*",A3:F3,A3:F3,"",2)

The 2 at the end is the wildcard option.

 

[Alex Blakenburg]

If you are trying to pick up a values you know to be offset from what you are looking for, Index Match is a better option than offset.
Offset is a volatile function and as such should only be used sparingly. If you use it on every row in a data sheet it will kill the performance of your spreadsheet.

So to return the value of your search for MyTest

=INDEX($A$3:$F$3,0,MATCH("MyTest"&"*",$A$3:$F$3,0))

To return the value of something you know is 2 columns to the right of this, add 2 to the column number returned by the match

=INDEX($A$3:$F$3,0,MATCH("MyTest"&"*",$A$3:$F$3,0)+2)

PS: If you prefer XMATCH you can use that instead of MATCH

 

[dotsent]

I am a bit confused. If that's all you are trying to do is return the cell value, almost any lookup function will do that. Xlookup would be the simplest.

=XLOOKUP("MyTest"&"*",A3:F3,A3:F3,"",2)

The 2 at the end is the wildcard option.

Thank you, perfect! XLOOKUP was the first option I was trying, but couldn't get it to work, ending up thinking it won't. My problem was using "Mytext*" instead of "Mytext" & "*"