Mike

[ismilewhenyousmile]

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.

 

[Dave Patton]

Excel 2010
	A	B	C	D
1	SX17010006	2017	2017	2017
2
2d

Cell Formulas
Range	Formula
B1	="20" & MID(A1,3,2)
C1	=("20" & MID(A1,3,2))+0
D1	=DATE(("20" & MID(A1,3,2))+0,1,1)

"Nonsense reply" - means the reply or the solution that prescribed does not make any sense because it did not fit to the purpose.

What information do you require?

N.B. D1 is a date formatted to show Year.
C1 is a number

 

[vcoolio]

What a pathetic reply to say the least.

You cross-posted here:-

Extract year from alphanumeric serial numbers.

and received a solution from a formula expert. You failed to tell anyone that you had cross-posted. Read the forum rules for this forum and stop wasting people's time.

The solution you received here and at the other forum absolutely fits your purpose in accordance with your explanation of the scenario.

Joe4's (and make note that he is an MVP) reply is not "any answer" and if you don't like it then we suggest that you go off and PAY a programmer to do your work and stop leeching off the Volunteers, here and other forums, who give their time freely to help the likes of you.

 

[Rick Rothstein]

The solution you received here and at the other forum absolutely fits your purpose in accordance with your explanation of the scenario.

I don't think I have seen one of these since the back in the olden days of newsgroups, but I wonder what is the possibility that we have a real, honest-to-goodness "troll" visiting us here?