Solved: Problem with Formula

[Blackie50]

The is a problem with the following formula

=LEFT(Sheet1!H1,4)&RIGHT(Sheet1!H1,3)+1

this copies and pastes 7 characters in total and adds 1 to the last number

First 3 chars always a letter
Last 3 chars always a number
middle char could be number or letter

JNPN456 becomes JNPN457

Problem is when a zero appears e.g. JNPN025 becomes JNPN26

Any one able to fix the formula

thanks in advance
Jon

[mbarron]

Use the TEXT function to add the zero padding:

=LEFT(Sheet1!H1,4)&TEXT(RIGHT(Sheet1!H1,3)+1,"000")

[Blackie50]

Thanks for your help

regards

Jon