MATCH function to match only alphabet letters

MannStewart

New Member
Joined
Oct 5, 2019
Messages
13
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,876
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,098
Messages
5,545,951
Members
410,713
Latest member
TaremyLunsil
Top