Unable to use vlookup on a trimmed value

**wtucker** · 06-14-2017, 02:06 PM

Hello everyone,

I'm in over my head and could really use some help.
I'm trying to create an access-linked spreadsheet to indicate a network(field) change from one contract period to another. My data is in tabular form and refreshes as new info enters our database.

Unfortunately, our ContractID is alphanumeric (begins with the letter D, the next 5 digits are the company's ID, and the last 4 are the year). To find the previous year's Network, I've trimmed the ContractID to remove the D in a separate column, then subtracted 1 from that value in a third column. I then run a vlookup to return the previous year's Network. Lastly, I have a formula to determine if the previous year's Network is different than the current year's.

I'm sure there are better ways to do this, but my VBA skills are like the fine motor skills of a toddler.

The issue I'm having is that the vlookup will not work against a trimmed value. I've learned that if I paste the value only, it will work. But because this needs to be actively linked to our Access database, I need it to update regularly without having to reformat the data every time.

The spreadsheet I've attached is a slimmed down version (we work with thousands of records), but the formulas are exactly as they should be. There are two tables, one that is the current version, and one with a few values pasted in to demonstrate the results I'm looking for.

Any help is greatly appreciated.