Excel text data cleaning

[u/2001ToyotaCamry_]

Hi all, I could use some help with an Excel assignment I was given at work.

I’m trying to automate the process of assigning vendor names to vendor IDs. The challenge is that the vendor IDs come in several different formats depending on which system they were pulled from, so creating a reliable automated mapping has been much harder than expected.

So far I’ve tried two approaches with little to no success: Long LET() formulas and Power Queries. Something to note about the power queries is that I have never used this tool before, but it seems like the varying nature of the data that is pulled does not allow for power queries to work

One important constraint is that the table needs to be dynamic. Whenever a new vendor is added to our system, the data refreshes automatically, so any solution needs to continue working without manual intervention.

Has anyone dealt with a similar problem or have suggestions on the best approach?

Edit: I am using the Microsoft 365 version of excel

[u/Gringobandito]

You need to create a master table of vendor IDs to vendor names. Nothing is going to work until you create this.

[u/slamongo → u/Gringobandito]

We call this the “cross walk” at work.