Solved: Help with Formula please

[Blackie50]

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

[Bob Phillips]

Try

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

[Tinbendr]

=CharsAndNums(Sheet1!H20)
[VBA]Function CharsAndNums(Target As String) As String
Dim A As Long
Dim lCount As Long
For A = 1 To Len(Target)
Select Case Asc(Mid(Target, A, 1))
Case 48 To 57 '0-9

Case Else
lCount = lCount + 1
End Select
Next
A = Val(Right(Target, Len(Target) - lCount)) + 1
CharsAndNums = Left(Target, lCount) & CStr(A)
End Function[/VBA]
David

[Blackie50]

Thanks Guys,

The formula works OK but can't seem to get the function to work

regards
Jon