Re-format a string & make the last 4 digits as "Year"

[mac_junior]

Hello

I have a string : ABC02102018 at cell A1. I need to make it looks like ABC 02/10/2018. I wrote =CONCATENATE(MID(A1,1,3), " ",MID(A1,4,2), "/",MID(A1,6,2), "/",MID(A1,8,4)).
Now I would like to make my last 4 digits of string (ie.2018) into YEAR format. I tried by using year() function, but it came out to 1905 instead of 2018. Does it have a way to solve it?

Thanks and regards,
John

 

[Rumburak]

It is not clear for me...
Maybe

=--MID(A1,8,4) or =MID(A1;8;4)+0

 

[RoryA]

What do you mean by year format? 2018 is a year format to my mind.

 

[mac_junior]

It is not clear for me...
Maybe

=--MID(A1,8,4) or =MID(A1;8;4)+0

thank you very much.

 

[mac_junior]

Yes I agree but 2018 just look like year format to us. When I put year(2018). I will output 1905. So, if I need to do some sorting in the future, it may go wrong.
Meanwhile, I was wondering if it can be achieved? I mean can I format part of the string which contain characters and digits.
Thanks again

 

[RoryA]

2018 is already a year number. Why would you use YEAR(2018)? The YEAR function returns a year number from a date, so it expects a date value, not just a year number. Since Excel stores dates as the number of days since 31/12/1899, YEAR(2018) is the equivalent of YEAR(10/07/1905) which is why you get 1905 back.

You can certainly format parts of a string, but it's not clear to me what formatting you are trying to apply.

 

[mac_junior]

2018 is already a year number. Why would you use YEAR(2018)? The YEAR function returns a year number from a date, so it expects a date value, not just a year number. Since Excel stores dates as the number of days since 31/12/1899, YEAR(2018) is the equivalent of YEAR(10/07/1905) which is why you get 1905 back.

You can certainly format parts of a string, but it's not clear to me what formatting you are trying to apply.

Rory, thank you very much. I will try to work use it without using format(2018).