PDA

View Full Version : Solved: Help with Formula please



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

Bob Phillips
11-23-2010, 09:32 AM
Try

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

Tinbendr
11-23-2010, 11:12 AM
=CharsAndNums(Sheet1!H20)
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
David

Blackie50
11-24-2010, 01:52 AM
Thanks Guys,

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

regards
Jon