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

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 wrote:

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

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


"Dave Peterson" wrote in message
...
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 wrote:

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