add digits to a CONCATENATE with embedded RIGHT function

[blbat]

The Right function in below formula pulls two digits from a string in G2. G2 = 282110ZOCT08

in the example the last two digits "08" represents the year 2008

=CONCATENATE(LEFT(G2,2),"-",MID(G2,8,3), "-",RIGHT(G2,2))
this results in:
28-OCT-08

I want to add 2000 to just the last segment so it reads 28-OCT-2008.

How can I do that?

 

[Rick Rothstein]

The Right function in below formula pulls two digits from a string in G2. G2 = 282110ZOCT08

in the example the last two digits "08" represents the year 2008

=CONCATENATE(LEFT(G2,2),"-",MID(G2,8,3), "-",RIGHT(G2,2))
this results in:
28-OCT-08

I want to add 2000 to just the last segment so it reads 28-OCT-2008.

How can I do that?

If your value in G2 (or whatever cell) is always 12 characters long (like the single example you posted), you can do it this way...

=UPPER(TEXT(REPLACE(G2,3,5,""),"dd-mmm-yyyy"))

otherwise, this way should work...

=UPPER(TEXT(LEFT(G2,2)&RIGHT(G2,5),"dd-mmm-yyyy"))

 

[hiker95]

blbat,

Try the following:

=CONCATENATE(LEFT(G2,2),"-",MID(G2,8,3), "-20",RIGHT(G2,2))

 

[Rick Rothstein]

If your value in G2 (or whatever cell) is always 12 characters long (like the single example you posted), you can do it this way...

=UPPER(TEXT(REPLACE(G2,3,5,""),"dd-mmm-yyyy"))

Actually, if your values are always 12 characters long, you could use this simpler formula...

=REPLACE(REPLACE(G2,3,5,"-"),7,0,"-20")

 

[blbat]

Thanks to you both.

Both work.

is there a way to force Excel to see the result as a Date? (without copying and pasting the values back into the column over the formula)

I want to be able to sort by oldest to newest.

 

[blbat]

Sorry Rick, forgot to answer you- YES, the value is always 12 digits.

 

[Rick Rothstein]

is there a way to force Excel to see the result as a Date? (without copying and pasting the values back into the column over the formula)

Sure... normally you would just add 0 to whatever formula you decide on, HOWEVER, since all you want is the real date, then the formula becomes even simpler (since we don't have to provide a formatted text value any more, rather, all we need is text that Excel can recognize is a date when we force it to do so by adding 0 to the formula). This formula will give you a real date as a date serial number so you will have to format the cell you put it in with the date format of your choosing...

=0+REPLACE(G2,3,5,"")

By the way, you should now see the benefit of telling what you actually want to do when you ask a question on this forum instead of assuming how it must be structured and asking how to achieve that structure. As in this case, we may know a simpler way to achieve your ultimate goal from the way you thought it would have to go (just look at how much simpler the formula I posted above is from all the other formulas that everyone here posted trying to get the text to look exactly like you showed us in your first message).

 

[blbat]

Rick-

Thanks again. and I will consider myself properly chastised on posting questions. Looking at my join date you would think I would know that by now. /doh!

I was so interested in the formula I posted that I never considered posting about the end result I was actually after!

 

[hiker95]

Thanks to you both.

Both work.

blbat,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.