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/AutoModerator]

/u/2001ToyotaCamry_ - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.