Finding wildcard match from table row (PDF file import)

dotsent

Board Regular
Joined
Feb 28, 2016
Messages
80
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!
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Alex Blakenburg

Active Member
Joined
Feb 23, 2021
Messages
380
Office Version
  1. 365
Platform
  1. Windows
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
 

dotsent

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

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

Active Member
Joined
Feb 23, 2021
Messages
380
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Alex Blakenburg

Active Member
Joined
Feb 23, 2021
Messages
380
Office Version
  1. 365
Platform
  1. Windows
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
 

dotsent

Board Regular
Joined
Feb 28, 2016
Messages
80
Office Version
  1. 365
Platform
  1. Windows
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" & "*"
 

Watch MrExcel Video

Forum statistics

Threads
1,127,134
Messages
5,622,930
Members
415,941
Latest member
georgiana686

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
Top