Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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?
|
#2
|
|||
|
|||
Quote:
Change cell for the relevant cell reference. For example =RIGHT(A1,6) |
#3
|
|||
|
|||
Quote:
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? |
#4
|
|||
|
|||
Quote:
|
#5
|
|||
|
|||
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...
|
#6
|
|||
|
|||
Which version of Excel are you using, Damien?
|
#7
|
|||
|
|||
|
#8
|
|||
|
|||
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? |
#9
|
|||
|
|||
Quote:
I hope this is more clear... |
#10
|
|||
|
|||
Quote:
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? |
#11
|
|||
|
|||
Quote:
|
#12
|
|||
|
|||
Quote:
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 :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading range w/ 255+ characters throws error | Excel Programming | |||
Reading the characters in a cell | Excel Programming | |||
Reading data arrays from multiple data files in excel | Excel Discussion (Misc queries) | |||
Reading data from PPT and purge data to Excel and image | Excel Programming | |||
Excel OLEDB reading Chinese characters problem | Excel Programming |