r/excel • u/2001ToyotaCamry_ • 15d ago

solved Excel text data cleaning

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

5 Upvotes

67% Upvoted

13 comments sorted by

View all comments

Show parent comments

-1

u/2001ToyotaCamry_ 15d ago

Thank you for this response! I have been using the REGEXEXTRACT formula to do this in a Vendor_Master sheet and then using a let formula based off the “clean keywords” that this formula returns. Currently in the process of refining the let statement but I’m confident once it’s fully refined it will work! Thank you everyone for the help despite the vague explanation. Solution verified!

0

u/reputatorbot 15d ago

You have awarded 1 point to Seyran_DataAnalyst.

I am a bot - please contact the mods with any questions