Incremanting text

[Francis Hookham]

Code please for incrementing text. For instance:



Text "D01-003" is in E7 and I need "D01-004" in E11



The following works but results in "D01-4" but I must maintain the "000"
format of the right three characters which need to go beyond "100"



Sub test()

Cells(11, 5) = Left(Cells(7, 5), 4) & (Right(Cells(7, 5), 3) + 1)

End Sub



Many thanks - in anticipation.



Francis Hookham

 

[Dave Peterson]

The function you're looking for is Format().

I'd use:

Cells(11, 5).Value = Left(Cells(7, 5).Value, 4) _
& Format(Mid(Cells(7, 5).Value, 5) + 1, "000")

With Mid(), you can just specify the starting position and xl's VBA will take
the rest of the string. (Different from the =mid() worksheet function.)

 

[Francis Hookham]

Many thanks, especially the Mid() tip - I did not know.