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.

 

[Joe4]

Welcome to the Board!

Weird thread title, but try this:

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

 

[ismilewhenyousmile]

Welcome to the Board!

Weird thread title, but try this:

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

Nonsense formula !

 

[steve the fish]

Don't think you do that much smiling by that reply!

 

[Joe4]

Nonsense formula !

Did you actually try it?

If you have "SX17010006" in cell A1, that formula will return "2017", which appears to be what you are after from your original question.

 

[63falcondude]

I think that was his way of saying that he liked it...

 

[FDibbins]

Nonsense formula !

wow, with replies like that, how much more help do you really expect?

 

[ismilewhenyousmile]

when someone put a question asking help, the responding person need to understand the query, and prior replying try to see if the answer really fits to the question. None have time to waste.

Throwing just "any answer" is a kind of fun, example a patient appear in hospital need assistance. Prior giving remedy, doctor need to understand whether the medication really fits to the patient.

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

 

[steve the fish]

Silly thing about your reply is that the formula provided does exactly what you asked for. As nonsense replies go it was anything but nonsense.

 

[MrIfOnly]

when someone put a question asking help, the responding person need to understand the query, and prior replying try to see if the answer really fits to the question. None have time to waste.

Throwing just "any answer" is a kind of fun, example a patient appear in hospital need assistance. Prior giving remedy, doctor need to understand whether the medication really fits to the patient.

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

1) You are soliciting free advice from unpaid volunteers so the presumably paid doctor analogy doesn't really hold up, but
2) Most doctors aren't going to tolerate rudeness any more than we will and posting "Nonsense reply" is just plain rude.
3) Joe's response was spot-on for the data sample you provided. If you didn't supply all of the relevant facts, then it's on you to elaborate. If you went to the doctor asking for a remedy without giving him all of the symptoms you will probably get a poor diagnosis. GIGO.