Extract year from alphanumeric serial numbers.

Closed
Mike - Updated on May 23, 2017 at 07:46 AM
Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
- May 25, 2017 at 02:34 PM
Hello,
Cell A1 = SX17010006
Cell A2 = SX17010006
Cell A3 = SX16120001
Cell A4 = SX15070004

I need a formula which read first four alphanumeric characters in any of above cells and return relevant year (i.e. 2017 or 2016 or 2015) basis finding. If it is SX17 then result should be 2017, if it is SX16 then result should be 2016, so on.

Kindly assist.

2 replies

Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
147
May 23, 2017 at 08:37 AM
Mike, Good morning.

Try to use:

=VALUE("20" & MID(A1,3,2))

Is this what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0
vcoolio
Posts
1347
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
June 10, 2022
249
May 24, 2017 at 08:06 AM
@Mazzaropi:-

Good day Mazzaropi,

You may be interested in this:-

https://www.mrexcel.com/board/threads/mike.1006589/

There are some ungrateful people out there! Beware!

Cheerio,
vcoolio.
0
Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
147
May 25, 2017 at 02:34 PM
vcoolio, Good afternoon.

Thank you for the warning.

Unfortunately, some are not grateful or polite to people who help them for free.

Not even so we will stop helping those in need because of this type of user.

Have a great day.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0