Increment alphanumeric string by 1 for each cell in Excel issue.

[strongman86]

Lads,

i have formula in cell A2: ''=LEFT(A1,4)&RIGHT(A1,4)+1'' that increments next cell by 1. Problem is when number go over 1000 it looks like this: HMR01000, but I need it to be HMR1000 , HMR1001 etc.

	A
1	HMR0500
2	HMR0501

<tbody>
</tbody>

Thanks.

 

[sykes]

Try: =LEFT(A1,3)&TEXT(RIGHT(A1,4)+1,"0000")

 

[bosco_yip]

Or,

In A2, copied down :

=TEXT(RIGHT(A1,4)+1,"""HMR""0000")

Regards
Bosco

 

[strongman86]

Thanks lads.