Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

# How to match and assign a word to a letter in a cell

Brass Contributor

# How to match and assign a word to a letter in a cell

Hi There,

I have a column of product numbers, within each number is one letter that represents the type of product it is. How do I marry  that letter up to a description from another list so that I can categorise every product number?

Example;

Product No.

226002T0049  this would be T for tyre.

What formula do I use to extract the letter T and assign it to the word Tyre?

19 Replies

# Re: How to match and assign a word to a letter in a cell

Provide few more sample and your desired output to make it clear. Better attach a sample file to your post so that we can download the file and work on it.

# Re: How to match and assign a word to a letter in a cell

@Eqa33 I would do that with Power Query. See attached.

# Re: How to match and assign a word to a letter in a cell

Hi @Eqa33

Assuming you run Excel 2021 or 365 a formula based alternative:

in C5:

``````=LET(
LetterPos, SUM(IFERROR(SEARCH(CHAR(SEQUENCE(26,,97)),B5),0)),
Letter,    MID(B5, LetterPos, 1),
Category,  XLOOKUP(Letter, TBL_MAPPING[Letter], TBL_MAPPING[Category]),
IF(LetterPos, Category, "No letter found")
)``````

# Re: How to match and assign a word to a letter in a cell

But how do I do that and then how does T become Tyre, and B become Bat, and C become Cap, etc?

# Re: How to match and assign a word to a letter in a cell

With @Riny_van_Eekelen 's data you can add another blue dynamic table where you enter the descriptions. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

# Re: How to match and assign a word to a letter in a cell

Sorry,but you need to expand your explanation. The end result you show is correct and what I want to achieve but what is the formula or process to look it up and extract the info?
Thanks very much

# Re: How to match and assign a word to a letter in a cell

This is done with Power Query. You can start the Power Query Editor as shown in the screenshot.

Then you can follow the applied steps within the Power Query Editor. "Angewendete Schritte" are the applied steps of the query. You can click on every step from "Source" to "Description" in order to follow the transformation of the data.

If you want to know more about Power Query i'd recommend this site:

Introduction to Power Query - Excel Off The Grid

# Re: How to match and assign a word to a letter in a cell

@OliverScheurich Ok, now I understand. But I have a problem when I merge the query I cannot I get a recognition of the letter buried in the middle of the product number it just produces a column of null and as consequence the category name is all null as well. What am I doing incorrectly? Thank you

# Re: How to match and assign a word to a letter in a cell

@Eqa33 Added a few steps to your query. These extract the letter codes and merge them with the ones in the Code table. See if this does what you need.

# Re: How to match and assign a word to a letter in a cell

Thanks for your answer. I am obviously doing something wrong. When I merge the 2 tables as you have shown I get null in every row. What am I doing incoirrectly?

# Re: How to match and assign a word to a letter in a cell

@Eqa33 Can't tell until you show me what you are doing.

# Re: How to match and assign a word to a letter in a cell

@Riny_van_Eekelen Not sure if this helps

# Re: How to match and assign a word to a letter in a cell

@Eqa33 Upload the file please. I need to see the Merge step. What you are merging and how. Did you extract the letter code in any of the other queries, by the way?

# Re: How to match and assign a word to a letter in a cell

@Riny_van_Eekelen Tell me if this is what you want to see ,please.

best response confirmed by Eqa33 (Brass Contributor)
Solution

# Re: How to match and assign a word to a letter in a cell

@Eqa33 You missed the most important part and that is that you need to extract the one Letter from the code first. You are now trying to merge the full product code in the Sales table with the category code in the Category table.  Obviously, that's not going to return any match.

I've added the step that extracts the code and changed the merge query. It works now.

# Re: How to match and assign a word to a letter in a cell

Thanks very much it works well now. I'm still not sure what step I missed. Could you explain it to me in simple language. Thank you once again

# Re: How to match and assign a word to a letter in a cell

@Eqa33 The last step of the 2022 Sales SOH query creates a custom column that takes the text from Column1 and removes any dot "." or number from "0" to "9", thus leaving only the single letter.

Now, in the Source step of the Merge1 query you select Custom and Code as the matching columns.

# Re: How to match and assign a word to a letter in a cell

Ok, I see know. I never would have thought to or known how to, enter that formula to seperate the Code Letters from the Product numbers. Thanks very much. I'm teaching myself Power Query. it's very powerful but also quite complex. Thanks for your help once again. Fantastic.
1 best response

Accepted Solutions
best response confirmed by Eqa33 (Brass Contributor)
Solution

# Re: How to match and assign a word to a letter in a cell

@Eqa33 You missed the most important part and that is that you need to extract the one Letter from the code first. You are now trying to merge the full product code in the Sales table with the category code in the Category table.  Obviously, that's not going to return any match.

I've added the step that extracts the code and changed the merge query. It works now.