Extracting information from a serial number into other cells.

[TitoBurrito]

So say I have a serial number in Cell A1 "22AA123456" I need 3 different formulas for three different cells.

Cell B1: This is grabbing the year. so the first 2 digits of the serial number would be the year. so 22 would be "2022" and that's what I need in B1.
Cell C1: This is grabbing the Month. (EX: A=January, B=Feb etc...) so the formula would see the 3rd index in the serial number string, so A, and spit out "January" in Cell C1.
Cell D1: This is grabbing the Location. (EX: A=America, M=Mexico) so the formula would see the 4th index as A and spit out "America" in Cell D1.

I'm having trouble telling it to look at like a specific index in the serial number. instead of saying like oh this cell contains the letter A so this is true, which don't work because the Month and Location have the same letters.

 

[Joe4]

Welcome to the Board!

The first two parts can be derived just from the data, because it is clear what each value represents.
But the third part, the mapping to a country, there needs to be a reference list somewhere that tells what each letter means (since it cannot be derived with simple logic).
Where exactly does this mapping occur? Is there some lookup table we can reference, or are there a very limited number of options?

 

[Joe4]

For the formulas for cells B1 and C1, you can use these:

B1:

="20"&LEFT(A1,2)

C1:

=TEXT(DATE(YEAR(TODAY()),CODE(MID(A1,3,1))-64,1),"mmmm")

For D1, let's say that you have a lookup list in cells G1:H10 that looks something like this:

Then you could have the following formula in cell D1 to return the country:

D1:

=VLOOKUP(MID(A1,4,1),$G$1:$H$10,2,0)

 

[TitoBurrito]

Wow thanks so much, this worked out really well.
I was able to follow and understand the formulas for B1 and D1.
But if you could explain the basics to C1 so i could understand it that would very much appreciated.

 

[Joe4]

But if you could explain the basics to C1 so i could understand it that would very much appreciated.

OK, so our goal is to try to get to the month name from a letter (A=January, B=February, etc).
The way I opted to do that was to build a valid date in Excel (using the "DATE" function), and then using the "TEXT" function to just return the full month name (i.e. TEXT(date, "mmmm")).

So, we first need to build a date using the DATE function. The format of the DATE function is:
DATE(year, month, day)
Since we really only care about the month, it doesn't really matter what we use for the Year and Day (as long as they are valid values).
If we use YEAR(TODAY()), that will return the year of the current date. So we can use that as our Year value.
And for the Day value, we can just use "1", as every month has a "1st".

The only trick left is how to get from the "letter" found in the string to the month number to use as the MONTH argument of the DATE function.
Let's break that apart.

First, we need to pull the third characters from our string. That is easy enough to do like this:
MID(A1,3,1)
So, now how to we convert "A" to 1, "B" to 2, "C" to 3, etc for the various months?

We can make use of ASCII codes. Every character on the keyboard has an ASCII code/number representing it (see: ASCII Code - The extended ASCII table).
The ASCII code for "A" is 65. And they got in order so that "B" is 66, "C" is 67, ..., "Z" is 90.
So we can make use of those by converting the letter to its corresponding ASCII code. We do that by using the CODE function. i.e.
CODE(MID(A1,3,1))

But now we have to convert those number to month numbers:

Letter	ASCII Code	Needed Month Number
A	65	1
B	66	2
C	67	3
D	68	4
E	69	5
F	70	6
G	71	7
H	72	8
I	73	9
J	74	10
K	75	11
L	76	12

Because they go in order, notice how the "Needed Month Number" is always exactly 64 less than the ASCII Code.
So all we have to do is then subtract 64 from our calculation to get the appropriate month number:
CODE(MID(A1,3,1))-64

If we were to break down the calcation, step-by-step, it would look something like this:
=TEXT(DATE(YEAR(TODAY()),CODE(MID(A1,3,1))-64,1),"mmmm")
=TEXT(DATE(2022,CODE("A")-64,1),"mmmm")
=TEXT(DATE(2022,65-64,1),"mmmm")
=TEXT(DATE(2022,1,1),"mmmm")
="January"

Does that explain it?

 

[TitoBurrito]

Wow Thank You for the detailed explanation! I didn't know about the ASCII codes.
Much appreciated.

 

[Joe4]

You are welcome!

Yes, that is a neat little trick we can use to easily convert those letters to numbers to allow us to do what we want.
There are other ways do it (for example, you could create your own lookup table and use a VLOOKUP formula), but that is a way that doesn't require any extra set-up.