Reading the last for characters of a data.
 

Hello. I have a list of values in a data that has 16 characters of numbers and letters (ie. 01V4245608099269). The only numbers/letters I need are the last 6 of the list. How would I create a formula that reads only the last 6?

=RIGHT(cell,6)

Change cell for the relevant cell reference. For example =RIGHT(A1,6)

Thanks! I was wondering if you could help me with something else too...
So in Column A, I have a list of numbers/letters (ie. 01V4245608099269), I want Column B to read the last 6 characters of Column A and if the number is 256, Column B should say Type1. If the number is 786, Column B should say Type2. Is this possible? How would I do that?

I dont understand how the last 6 characters would be a 3 digit number. Could you explain a little more?

The Type (Type1, Type2, etc) can either be 2 or 3 digits. However, it's within the last 6 digits of the data. So if I'm looking for 256, it could be BKA256, or 256123, or B256KA. I hope this makes sense...

Which version of Excel are you using, Damien?

Excel 2010.

Sorry for all the questions, but the explanation was slightly bereft of detail.

So you're looking for Type 1 if the last 6 digits contain 256 and Type 2 if the last 6 digits contain 786. What happens if neither appear in the last 6 digits?

Also, are there values for Type 3, Type 4 etc or just the 2 types?

No, no problem at all. Any information would be helpful. What I'm doing is pulling a report (through another program) that gives out a long list of serial numbers (ie. 01V42466220BH315). These serial numbers are the machines that we have out in the field. From these serial numbers, the last 6 characters will identify what type of machine it is (there are 14 types). If the numbers do not match one of the 14 types, then the machine is not serviced by our location. I have 14 point of contacts (1 for each type of machine). What I'm trying to do-- get EXCEL to read the last 6 digits of the serial number, determine what type it is, and list the point of contact for that type. So I'm pasting the serial numbers into Column A. I want Column B to read the last 6 digits of Column A and determine the type of machine it is. (Of the last six digits, only three in a specific order are important [ie. LOOK for 236 from BK2365]) Then, I would like for Column C to read the type on Column B and determine who the point of contact is.

I hope this is more clear...

What you're trying to do makes perfect sense now. Exactly HOW you do that is still a little fuzzy in my brain.

Leave it with me and hopefully I'll be back soon with an answer.
Failing that, someone else may pop up and help you out.

Just for convenience sake, what are the 14 three digit codes in question?

205, 209, 235, 236, 237, 238, 239, BH1, BV6, ANR, ANP, FW3, BK1, BK2

As luck would have it, whilst trawling through several other forums for the answer to this, someone asked almost exactly the same question.

So I took that formula and adapted it slightly to fit your needs.

With this version you don't even need the column B that extracts just the last six digits, it merely searches the last six digits of each entry in column A directly.

Hopefully you can adapt it to meet the requirements of your specific workbook, although I'm happy to assist you with that too if need be.

Please don't ask me to explain exactly how it works as I'm still trying to fathom that myself :)