Finding wildcard match from table row (PDF file import)

dotsent

Board Regular
Joined
Feb 28, 2016
Messages
85
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.

VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
Excel Formula:
=XLOOKUP("MyTest"&"*",A3:F3,A3:F3,"",2)

The 2 at the end is the wildcard option.
 
Upvote 0
Solution
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
Excel Formula:
=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
Excel Formula:
=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
 
Upvote 0
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.
Excel Formula:
=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" & "*"
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top