Incremanting text

F

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
 
D

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.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top