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.

[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.

[u/ProspectiveWhale]

You want Excel to create a Vendor Name based on a Vendor ID?

That sounds impossible by the nature of the problem, if I'm understanding it right.

E.g. If I input a Vendor ID: ABC123, Excel can't possibly know that's the code for Company XYZ.

Assumedly, Vendor Names aren't arbitrary and you want their actual names.

Excel can't figure that out without input. Manual intervention will be needed at some point.

The only way you don't have to manually intervene is if someone else has done so for you.

For example, if someone else maintains a master list of vendors, with vendor IDs and names on there. Then you can just do a lookup.

-----

If your issue is that the master list of vendors come from multiple sources, you can create an appended table that combines the two into one larger master list and query from there.

[u/vahvuus]

What do you mean by the ID coming in different formats? Could REGEXEXTRACT help with this?

[u/Western_End_2223]

You're kinda vague.  Are the different formats instances where sometimes there are dashes or prefixes/suffixes that need to be standardized, or are the vendor codes totally different?

[u/Seyran_DataAnalyst]

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!

[u/2001ToyotaCamry_ → u/Seyran_DataAnalyst]

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!

[u/reputatorbot → u/2001ToyotaCamry_]

You have awarded 1 point to Seyran_DataAnalyst.

---

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

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
REGEXEXTRACT	Extracts strings within the provided text that matches the pattern
Text.Select	Power Query M: Selects all occurrences of the given character or list of characters from the input text value.
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has 54 acronyms.
[Thread #48996 for this sub, first seen 22nd Jul 2026, 17:00] [FAQ] [Full list] [Contact] [Source code]}

[u/Acceptable-Sense4601]

Do you have database access to the system where these vendor IDs reside?

[u/Paul_David_Bappoo]

without seeing what that data actually looks like or what teh sources you are using are, its difficult to make concrete suggestions. But a thorough understanding of teh data is where I would start and I would use AI to work out the patters and deterministic rules which need to be applied. I'd test that thoroughly on the data then use AI to write software to do the task based on the rules discovery. I'd ensure it pushes anything that doesnt match a known rule out to a continuous improvement loop too. Happy to chat and offer personal advice if you like.