MATCH function to match only alphabet letters

MannStewart

New Member
Joined
Oct 5, 2019
Messages
14
Hi

Can anyone guide me in how to tweak my MATCH function?

Issue is the MATCH function would overlook the source data when the source file provider (for some silly reason) randomly attaches some random ASCII characters to some of the cells contents, for eg. if a DOT or a COMMA is attached to the letters, the MATCH will return nothing or not found when the item is there in the source. The MATCH function just works perfectly fine when the source data comes clean with no ASCII characters attached, and I have no way to control how the source organises the data contents.

Say, one of my symbol is "TYPH02" which corresponds to looked for data item in source file is TYPH02 that INDEXES for 2020_06_22 in their column A:A which I need for my workbook, but the provider somehow input the item as "TYPH02." into their cell (note there's a dot immediately after the item), and this rendered my function blind & cannot match anything. Otherwise, all other items that are "clean" could be MATCHED & INDEXED. I would guess my solution is to have soem form of modification to the MATCH function to work even if the provider attached some random ASCII characters to it.

Here's my existing function: =IF(symbol<>"",INDEX(source.xls!$A$2:$A$5000,MATCH(symbol,source.xls!$W$2:$W$5000,0)),"")



Stewart
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Could we actually see some of the column W data (not 5000 rows!) from source.xls so we can see what if any pattern/consistency there is with the data that will appear there?

Could we also see some more of the 'rogue' data that you would be trying to match so that we get a better feel for what you are dealing with?

In both cases it would help if it was provided with XL2BB so that we can easily copy & use for testing
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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