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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]HMR0500[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]HMR0501[/TD]
[/TR]
</tbody>[/TABLE]

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.