Adding or subtracting 1 from number value in cell that also contains text

[LeonardH]

Hello Everyone,

I'm looking for a formula that would cause a number value to either increase or decrease by one. Examples of text include: Apr Y0, May Y0, Jun Y1, Jul Y4. The common denominator is that the number is always preceded by " Y".

Thanks in advance for all your expertise! Please let me know if you have any questions.

 

[gaz_chops]

If it is always 5 characters before the number, then is this what you are after?

=LEFT(A1,5)&REPLACE(A1,1,5,"")+C1

 

[LeonardH]

So, not quite. And let me add a little more, with more context. I also found that I'll need to subtract instead of add... This will make it tricky

The bottom row is a sample of the values that will be queried into my worksheet. The top row is an example of the values I would like to see.

Nov Y-1	Dec Y-1	Year-1	Jan Y0	Feb Y0	Mar Y0	Y0 Q2	Y0 Q3	Y0 Q4	Year0
Nov Y0	Dec Y0	Year0	Jan Y1	Feb Y1	Mar Y1	Y1 Q2	Y1 Q3	Y1 Q4	Year1

So far to get the "Nov Y-1", I am using:

=(LEFT(A2,SEARCH(" Y",A2)+1)&RIGHT(A2,LEN(A2)-(SEARCH(" Y",A2)+1))-1)

Now the tricky part is coming up with if functions that also address the other two formats: Year# and Y# Q#. The only numbers that should change are the ones associated with years.

Thank you for everyone's help. This one is a challenge.