Breaking split at not 0

[Lavina]

Hello guys,

I have a sequence:
[TABLE="width: 97"]
<colgroup><col></colgroup><tbody>[TR]
[TD]00000001
[/TD]
[/TR]
[TR]
[TD]00000005
[/TD]
[/TR]
[TR]
[TD]00000009
[/TD]
[/TR]
[TR]
[TD]00000013
[/TD]
[/TR]
[TR]
[TD]00000017
[/TD]
[/TR]
[TR]
[TD]00000021
[/TD]
[/TR]
</tbody>[/TABLE]

I want to extend it indefinitely, well until it fits, but i was not sure what the best approach would be.
I was thinking of taking it as a string and use split to the first non 0, then turn that into a long add 4 to it and then switch it into a string and fill it up with 0 to make it an 8 digit. But i was unsure of how i can formulate my split. Any ideas? Or maybe a better approach?

 

[Fluff]

Assuming the first value is in A2, try this in A3 & copy down
=TEXT(A2+4,"00000000")

 

[sandy666]

or A2: =BASE(A1+4,10,8) where A1: 00000001

 

[Rick Rothstein]

or A2: =BASE(A1+4,10,8) where A1: 00000001

I am guessing BASE was added in one of the more recent versions of Excel... I am using XL2010 and it is not in my version. Fluff had alread posted the formula that I was going to post.

 

[sandy666]

BASE function

Excel for Office 365 Excel for Office 365 for Mac Excel for the web Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2016 for Mac Excel for Mac 2011

Post #1 does not contain Excel version

 

[Rick Rothstein]

BASE function

Excel for Office 365 Excel for Office 365 for Mac Excel for the web Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2016 for Mac Excel for Mac 2011

Post #1 does not contain Excel version

Thanks for the info. Strange... the Mac versions of Excel got the BASE function years before the Windows version?

 

[sandy666]

It's info from MS site, I won't argue with MS

Link: Base()

 

[Lavina]

Sorry guys, slacked off on creating a proper question, thank you for your input though.

Since i needed jumps to happen at specific times i ended up with something like this:

Sub Sequence()
Dim lastRow As Long, i As Long, currentNumber As Long
lastRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
currentNumber = 1
For i = 2 To lastRow
Cells(i, 14) = currentNumber
    If Cells(i, 14) = Len(Cells(i, 14)) <> 12 Then
        For j = 1 To 12 - Len(Cells(i, 14))
        Cells(i, 14) = "0" & Cells(i, 14)
        Next j
    End If
currentNumber = currentNumber + 5
If Cells(i, 8) <> Cells(i + 1, 8) Then currentNumber = currentNumber + 100
If Cells(i, 5) <> Cells(i + 1, 5) Then currentNumber = currentNumber + 200
Next i
End Sub

 

[Fluff]

Glad you sorted it & thanks for the feedback