Microsoft Excel - Add +1 to Value at end of text string

Asked By C R on 16-Feb-12 03:03 PM

How would i change the value at the end of a text string? For example, if my text string reads "Page 2", is there a formula that would add +1 to this string so it would read "Page 3"? I would also need this formula to apply for two-digit numbers (ie. a formula that would work when going from Page 9 to Page 10.

wally eye replied to C R on 16-Feb-12 04:55 PM
If you know it will always be:

Page xxxx

You can do something like this:

=Left(strMyPage,5) & cstr(cint(right(strMyPage,len(strMyPage)-5))+1)

Otherwise, if the number will always be a the end:

    Dim strMyPage As String
    Dim intPos    As Integer

    strMyPage = "Page lkl"
    For intPos = Len(strMyPage) To 1 Step -1
      If Not IsNumeric(Mid(strMyPage, intPos, 1)) Then
        Exit For
      End If
    Next intPos

    If intPos < Len(strMyPage) Then
      strMyPage = Left(strMyPage, intPos) & CStr(CInt(Right(strMyPage, Len(strMyPage) - intPos)) + 1)
    End If

There might be a better way to do this, if you could put some context around how you are using it.  I'm assuming you are doing this in VBA, a formula would look something like this:

=LEFT(B6,FIND(" ", B6)) & RIGHT(B6,LEN(B6)-FIND(" ", B6)) +1

or even better would to be to just use the numbers and change the number format to:

"Page "#0

C R replied to wally eye on 17-Feb-12 10:25 AM
Thank you. this helps.

In VBA, how would I write a macro to rename all active workbook tabs based on a range of cells ("A20:A39") located in my first workbook tab?
wally eye replied to C R on 17-Feb-12 01:50 PM
Something like:

public sub RenameWorksheets(byval rngNames as excel.range)

  dim wksCurr        as worksheet
  dim arrNames      as variant

  dim intSheet        as integer

  arrnames = rngnames.value
  for each wkscurr in thisworkbook.worksheets
    if wkscurr.index <= ubound(arrnames) then = arrnames(wkscurr.index,1)
  next wkscurr

  set rngnames = nothing

exit sub

Assuming you aren't assigning invalid names and are not changing a worksheet name to one that already exists.  If you need to do this then you will want to put some validation and other checks in.

Just call it with another procedure, say a button_click:

public sub btnUpdateSheets()

  call RenameWorksheets(worksheets("Sheet1").Range("A2:A17"))

end sub

where Sheet1!A2:A17 has the new list of names.  Or you can just run this directly from VBA.