Previous Topic Next Topic

Extract year from alphanumeric serial numbers.

Closed
Mike - Updated on May 23, 2017 at 07:46 AM
Mazzaropi Posts 1983 Registration date Monday August 16, 2010 Status Contributor Last seen February 27, 2023 - 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

Answer 1 / 2
Mazzaropi Posts 1983 Registration date Monday August 16, 2010 Status Contributor Last seen February 27, 2023 146
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
Answer 2 / 2
vcoolio Posts 1366 Registration date Thursday July 24, 2014 Status Moderator Last seen March 29, 2023 252
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 1983 Registration date Monday August 16, 2010 Status Contributor Last seen February 27, 2023 146
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