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

[Eqa33]

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?

thanks for your help.

[#2]

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.

[#3]

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

[#4]

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")
)

[#5]

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

[#6]

@Harun24HR 

[#7]

@Eqa33 

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.

[#8]

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

[#9]

@Eqa33 

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

[#10]

@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

[#11]

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

[#12]

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?

[#13]

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

[#14]

@Riny_van_Eekelen Not sure if this helps

[#15]

@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?

[#16]

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

[#17 · Accepted solution]

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

[#18]

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

[#19]

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

[#20]

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.