Blackie50
11-23-2010, 08:53 AM
Hi
I have the following formula which copies a 7 characters from sheet1 to sheet 2 - the characters could be 3 letters & 4 numbers OR 4 letters and 3 numbers. It also adds 1 to the sequence
=LEFT(Sheet1!H20,4)&RIGHT(Sheet1!H20,3)+1
works fine when there are 4 letters and 3 numbers e.g. YYYY111 goes to YYYY112, but when YYY6005 it comes out as YYY66 - loses the 2 noughts.
In this situation it works OK if change to
=LEFT(Sheet1!H20,3)&RIGHT(Sheet1!H20,4)+1
Is there a way to get these 2 formulas in to one to solve or maybe something in VBA?
thanks
Jon
I have the following formula which copies a 7 characters from sheet1 to sheet 2 - the characters could be 3 letters & 4 numbers OR 4 letters and 3 numbers. It also adds 1 to the sequence
=LEFT(Sheet1!H20,4)&RIGHT(Sheet1!H20,3)+1
works fine when there are 4 letters and 3 numbers e.g. YYYY111 goes to YYYY112, but when YYY6005 it comes out as YYY66 - loses the 2 noughts.
In this situation it works OK if change to
=LEFT(Sheet1!H20,3)&RIGHT(Sheet1!H20,4)+1
Is there a way to get these 2 formulas in to one to solve or maybe something in VBA?
thanks
Jon