VBA help - Rollforward date within text

DaniNaj

New Member
Joined
Nov 1, 2019
Messages
9
Hi,

I was wondering if anyone could help me as i'm a bit stuck on the following task. I need to automate the roll-forward of an excel spreadsheet where the year which is being increased by 1 is within text.

ABC2019ABCDEFG will need to be roll-forwarded to ABC2020ABCDEFG.

The issues I'm having is that not all of the format is the same; some use the format for the year "2019" whereas others use "19". The location of the year is also not always the same within the text, e.g:

ABC2019ABCDEFG or ABCDE2019ABCDE

Also, not all of the years are 2019 - some of the columns are using last year (2018) and other's use 2020 already.

Any help/guidance would be greatly appreciated.

Thanks in advance.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,835
Is the year the only numeric value within the text?

If so, try this:

Code:
Sub IncrementYear()
Dim c As Range, lngYear As Long
For Each c In Range(Range("A1"), Range("A1").End(xlToRight))
    lngYear = GetNumber(c.Text)
    If lngYear <> 0 Then c = Replace(c, lngYear, lngYear + 1)
Next
End Sub

Function GetNumber(r As String) As Long
Dim x As Long, strNum As String, strChar As String
For x = 1 To Len(r)
    strChar = Mid(r, x, 1)
    If IsNumeric(strChar) Then
        GetNumber = Val(Mid(r, x))
        Exit For
    End If
Next
End Function
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,746
Office Version
2007
Platform
Windows
Hi @DaniNaj, welcome to the forum!

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:179.64px;" /><col style="width:135.92px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >ABC2019ABCDEFG</td><td style="text-align:right; ">2020</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >ABC19ABCDEFG</td><td style="text-align:right; ">20</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >ABCDE2019ABCDE</td><td style="text-align:right; ">2020</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >ABCDE2018ABCDE</td><td style="text-align:right; ">2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >ABCDE2020ABCDE</td><td style="text-align:right; ">2021</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10) + 1</td></tr></table></td></tr></table>



Reference :https://www.extendoffice.com/documen...om-string.html
 
Last edited:

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,486
A couple more options.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">AlphaNumeric</td><td style="text-align: right;;"></td><td style=";">UDF</td><td style="text-align: right;;"></td><td style=";">AlphaNumeric</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="color: #333333;;">ABC2019ABCDEFG</td><td style="text-align: right;color: #333333;;"></td><td style="color: #333333;;">ABC2020ABCDEFG</td><td style="text-align: right;;"></td><td style=";">ABC2020ABCDEFG</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">ABC123GHRFDS</td><td style="text-align: right;;"></td><td style="color: #333333;;">ABC123GHRFDS</td><td style="text-align: right;;"></td><td style=";">ABC123GHRFDS</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=RollOver(<font color="Blue">A2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=RollOver(<font color="Blue">A3</font>)</td></tr></tbody></table></td></tr></table><br />

UDF Code
Code:
Function RollOver(s As String) As String
Dim yr As Integer: yr = 0


With CreateObject("VBScript.RegExp")
    .Pattern = "\d{4}"
    If .test(s) Then
        yr = Int(.Execute(s)(0)) + 1
        RollOver = .Replace(s, yr)
    Else
        RollOver = s
    End If
End With


End Function
Power Query M Code
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Extract = Table.AddColumn(Source, "Custom", each try Number.FromText(Text.Range([AlphaNumeric],Text.PositionOfAny([AlphaNumeric],{"0".."9"}),4)) otherwise [AlphaNumeric]),
    Final = Table.AddColumn(Extract, "Custom.1", each if [AlphaNumeric]=[Custom] then [AlphaNumeric] else Text.Replace([AlphaNumeric],Text.From([Custom]),Text.From([Custom]+1))),
    Remove = Table.RemoveColumns(Final,{"AlphaNumeric", "Custom"}),
    Renamed = Table.RenameColumns(Remove,{{"Custom.1", "AlphaNumeric"}})
in
    Renamed
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,746
Office Version
2007
Platform
Windows
I missed the auxiliary column with the final result:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:179.64px;" /><col style="width:135.92px;" /><col style="width:112.16px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >ABC2019ABCDEFG</td><td style="text-align:right; ">2019</td><td >ABC2020ABCDEFG</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >ABC19ABCDEFG</td><td style="text-align:right; ">19</td><td >ABC20ABCDEFG</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >ABCDE2019ABCDE</td><td style="text-align:right; ">2019</td><td >ABCDE2020ABCDE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >ABCDE2018ABCDE</td><td style="text-align:right; ">2018</td><td >ABCDE2019ABCDE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >ABCDE2020ABCDE</td><td style="text-align:right; ">2020</td><td >ABCDE2021ABCDE</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)</td></tr><tr><td >C2</td><td >=SUBSTITUTE(A2,B2,B2+1)</td></tr></table></td></tr></table>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,722
Office Version
365
Platform
Windows
If your problem has not been resolved already by one of the suggestions, can you clarify what should happen with examples like these?
Also, not all of the years are 2019 - some of the columns are using last year (2018) and other's use 2020 already.
- Do they just roll forward a year too?, or
- should they all become 2020?, or
- something else?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,746
Office Version
2007
Platform
Windows
If at the moment they are only the years 18,19 and 20, it could be:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:179.64px;" /><col style="width:179.64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >ABC2019ABCDEFG</td><td >ABC2020ABCDEFG</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >ABC19ABCDEFG</td><td >ABC20ABCDEFG</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >ABCDE2019ABCDE</td><td >ABCDE2020ABCDE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >ABCDE2018ABCDE</td><td >ABCDE2019ABCDE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >ABCDE2020ABCDE</td><td >ABCDE2021ABCDE</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,2020,2021),19,20),18,19)</td></tr></table></td></tr></table>
 

DaniNaj

New Member
Joined
Nov 1, 2019
Messages
9
I need some help with the following roll-forward task.

I have an Excel spreadsheet that contains many jobs that need to be roll forwarded to the next year. Previously this has been manually rolled forward to reflect the next year.

(All 2010 were manually changed to 2011, 2017 to 2018, 15 to 16, 12 to 13 etc...)

I was hoping there was a way to automate the process as it will save a lot of time however I haven't had much luck in doing so.

The first issue is that all the Jobs are not in the same format, the length of each job is different and so is the year format. Some take YYYY as the format whereas other's take YYYY.

So, here are 3 examples:

1) ABCDEFGHIJK_ABCD12 -> ABCDEFGHIJK_ABCD13

2) ABCDEFGHIJK_ABCD2012 -> ABCDEFGHIJK_ABCD2013

3) ^ABCDEFGH^/AbcdefGHIJ.ab2019^ABC^ -> ^ABCDEFGH^/AbcdefGHIJ.ab2020^ABC^


However, there are columns within the spreadsheet we don't want to rollforward, these are in the format:

ABCDEFGHI01ABCDE to NOT CHANGE to ABCDEFGHI02ABCDE; we want to keep it the same, the numbers here do not correspond to the year.

So the first question I have is can this even be done as previous solutions that have been proposed will change the cells that I want to keep the same.

Thank you in advance
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,722
Office Version
365
Platform
Windows
Since this is a duplication/continuation of your previous question, I have moved it to that thread. Refer to number 12 of the Forum Rules and number 6 of the Forum Use Guidelines.
 

Forum statistics

Threads
1,077,822
Messages
5,336,566
Members
399,088
Latest member
Swindlestikz

Some videos you may like

This Week's Hot Topics

Top