How to extract date from middle of text string and add one month to the date.

Hi excel forum,
I would like to take the month and year that is listed in the last 6 characters of the strings in column A and add one month to them.
My desired result would be, e.g. for A2, "Monthly ISEA Progress Report - Oct 21" -> "Nov 21"
For extra context, there is not always a " - " before the Date so using the function: =RIGHT(B5,LEN(B5)-FIND(" - ",B5))
will not work for all cases.

Objective 1:
What I have been trying to achieve is adding a month to the date using the DATE function of:
A2 = Monthly ISEA Progress Report - Oct 21
=TEXT(DATE(RIGHT(A2,2),RIGHT(A2,6)-RIGHT(A2,2),),"MMM YY")
But this does not work as Excel does not recognise this as a date.
RIGHT(A2,2) = 21, RIGHT(A2,6) = Oct 21
How do I extract just the "Oct" part of the string, given that:
1. There is not always a " - " preceding the date.
2. There is ALWAYS 6 characters from the right to include the whole date as a consistency.

Objective 2:
Once this month has been extracted, to be able to put it into the form of =TEXT(DATE(YEAR, MONTH,),"MMM YY") but with the Month (+1)

If his doesn't make sense please let me know! Sheet Attached!

in B2 with this:

Please Login or Register  to view this content.

in E2:

Please Login or Register  to view this content.

in F2

Please Login or Register  to view this content.

custom format cell E2 with this:

Please Login or Register  to view this content.

custom format cell F2

Please Login or Register  to view this content.

select B2,E2,F2, and drag down

try this formula

Formula:

Please Login or Register  to view this content.

set cells format as "MMM-YY"

Originally Posted by Pete_UK

You can use this formula to give you the next month:

=TEXT(EOMONTH(DATEVALUE(1&RIGHT($A2,6)),0)+1,"mmm-yy")

Copy down as required.

Hope this helps.

Pete

Originally Posted by Phuocam

Try this:

=TEXT(EDATE("1"&RIGHT(A2,6),1),"MMM-YY")

both of these worked really well thank you!

Would you be able to explain what the function of the "1&RIGHT" part is? Why is there a 1 there, what does it do?
EDIT: I see what it does, it adds on 1 to the date, so that the date is a full date e.g. 1May 22, then the other 1 adds one to the month as required, thanks!