Forum Discussion
Convert information in a cell into words
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.
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)
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!