SOLVED

Convert information in a cell into words

Copper Contributor
Good day,
I'm new to the message boards and only know basic Excel. I need to find a way for Excel to take the information in a cell and automatically convert it into words.
I receive emails with color codes that I need to be converted into the actual color, ie 06X1 = bronze oxide. At this time it has to be done manually, which is a pain.
Any help would be appreciated.
10 Replies

Do you have a list of all of the codes and color names that you reference then?

 

Is the color code in cell by itself?

I do have an Excel sheet with the codes and what color they reference, they are separated into different cells. The Excel sheet sent by email has the code in its own cell as well. The sheet references cars we receive in so there are columns for Vin, stock #, color code, etc.

OK, This should be fairly straightforward, are you able to share a partial sample? (I'll walk you through this either way but as you said your Excel knowledge is pretty basic, would be easier if you could share a sample)

I attached a sample. I hope it helps. 

OK, I am going to assume your other sheet has 2 columns one with the paint code and one with the name.

 

In the cell to the right of 06X1 on the sheet you posted (I am assuming 06X1 is in F1, so the cell to the right would be G1) enter     =Vlookup(F1,    Then select the columns on the sheet with the paint codes and paint names,    then  ,2,false) then hit enter, you should be able to ten copy the formula down column G to populate everything on the sheet. 

 

The formula should read:   =VLOOKUP(F1,[Book2]Sheet1!$A:$B,2,FALSE)

 

where [Book2] is the name of the workbook and Sheet1 is the name of the sheet in that workbook with all of the paint colors codes

 

Again, I am assuming the workbook containing the codes/color names has color code in Column A and Color name in column B, if that's not the case let me know and we can revise this. 

So, the color codes are in the same workbook, just sheet 2 named Color. I don't know if that changes the formula at all. I obviously did something wrong as I am getting a #N/A error. Mine ended up looking like this. I apologize if I butchered it at all...

 

=VLOOKUP(F1,[KeyTags.xlsx]Color!$A66:$B66,2,FALSE)

 

 

best response confirmed by Xaphier (Copper Contributor)
Solution

OK, So first off my apologies as I didn't realize I could actually download what you posted (Thought it was a screenshot, just joined the message board yesterday!), I am an idiot and probably shouldn't have been trying to help anyone yesterday. Very sorry. 

 

Use this in G1, I tested it and it seems to work for me:

 

=VLOOKUP(IF(LEN(F1)>3,RIGHT(F1,3),(IF(LEN(F1)=2,"0"&F1,F1))),Color!A:B,2,FALSE)

 

Had to get a little more complicated with the formula as it looks like sometime you get the paint codes as 4 digit codes with an extra leading 0 and sometimes a 2 digit code with a 0 dropped off the front (In relation to the all the codes being 3 digits codes on your sheet "Color"). This is essentially the same formula I sent before but I added: If the paint code we want to look up is 4 digits, remove the first digit, If it's 2 digits add a "0" to the front, thereby always ending up with a 3 digit code to lookup on your other sheet. 

OMG, it worked. You're brillant. Thank you so much!

Your welcome. Sorry again for the initial run around. Feel free to send me a note if you need a hand with anything else (Shiuld be much quicker/smoother now that I have some grasp on how the forum works...)

You got it. Thank you again

1 best response

Accepted Solutions
best response confirmed by Xaphier (Copper Contributor)
Solution

OK, So first off my apologies as I didn't realize I could actually download what you posted (Thought it was a screenshot, just joined the message board yesterday!), I am an idiot and probably shouldn't have been trying to help anyone yesterday. Very sorry. 

 

Use this in G1, I tested it and it seems to work for me:

 

=VLOOKUP(IF(LEN(F1)>3,RIGHT(F1,3),(IF(LEN(F1)=2,"0"&F1,F1))),Color!A:B,2,FALSE)

 

Had to get a little more complicated with the formula as it looks like sometime you get the paint codes as 4 digit codes with an extra leading 0 and sometimes a 2 digit code with a 0 dropped off the front (In relation to the all the codes being 3 digits codes on your sheet "Color"). This is essentially the same formula I sent before but I added: If the paint code we want to look up is 4 digits, remove the first digit, If it's 2 digits add a "0" to the front, thereby always ending up with a 3 digit code to lookup on your other sheet. 

View solution in original post