Excel Formula extract value and add number 3

[telecomadmin12]

I got a cell A1 with "r61".
I want to have a formula that adds "1" to the value in cell A1 with the output being "r62".

How would I achieve that?

Thanks.

 

[SkipVought]

Hi,

For that very specific instance (which may not be typical of any other instance)
[tt]
=left(A1,1)&right(A1,2)+1
[/tt]

 

[telecomadmin12]

Exactly what I needed.

Thank you.

 

[telecomadmin12]

Except that it breaks when the value exceeds 100.
If cell P36 looks like this: "r99" or higher, then:
"=LEFT(P36,1)&RIGHT(P36,2)+1" breaks.

What to change?

 

[SkipVought]

As I previously stated, for this specific instance...

...and you proved my caveat!

So let's look at the general requirements.

Will there ALWAYS and ONLY be ONE alpha character followed by ONLY numeric digits?

 

[telecomadmin12]

Starting with r100, "=LEFT(P36,1)&RIGHT(P36,3)+1" does it.
However is there a formula, that can cope with a changing number of digits?

 

[SkipVought]

First answer my question.

 

[telecomadmin12]

Yes, the alpha character will always be "r" followed by numeric digits between 1 and 999.

 

[SkipVought]

We'll he formula for the posted instances will break if the numeric part is between 1 & 9 and 100 & 999! That's what happens when you don't give enough information, which I anticipated!
[tt]
=left(A1,1)&right(A1,len(A1)-1)
[/tt]

 

[telecomadmin12]

=left(A1,1)&right(A1,len(A1)-1)+1 made it work.
Thanks a lot for your help!

 

[Andrzejek]

[tt]
=left(A1,1)&right(A1,len(A1)-1)[blue] + 1[/blue]
[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Sorry, but glad you could work it out.

 

[Andrzejek]

telecomadmin12,
Mark Skip's helpful post with the star by clicking on [blue]"Great Post? Star it."[/blue]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.