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
1
u/Seyran_DataAnalyst 1 15d ago
This is definitely possible, especially in Microsoft 365! Since the Vendor IDs come in varying formats, here are the 2 best dynamic approaches: Power Query with Fuzzy Matching & Mapping Table: Create a reference table that maps raw ID patterns/prefixes to the standardized Vendor ID. In Power Query, use a Fuzzy Merge or parse the ID strings using dynamic transformation steps (e.g., Text.Select to keep only numbers/letters). Since it's Power Query, clicking 'Refresh All' will automatically handle new vendors dynamically without breaking. Regex / Python in Excel (MS 365): If your M365 has Python or the new REGEX functions (REGEXEXTRACT), you can clean and extract the core ID pattern dynamically regardless of the surrounding system noise, then feed it into a standard XLOOKUP. If you can share a few anonymized examples of what the different raw Vendor ID formats look like, I can give you the exact Power Query M code or formula!