Forum Discussion

Eqa33's avatar
Eqa33
Brass Contributor
Dec 03, 2022

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?

 

thanks for your help.

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

     

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    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.
    • Eqa33's avatar
      Eqa33
      Brass Contributor
      But how do I do that and then how does T become Tyre, and B become Bat, and C become Cap, etc?
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor

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

Resources