VBA help - Rollforward date within text

DaniNaj

New Member
Joined
Nov 1, 2019
Messages
11
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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:
Upvote 0
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:
Upvote 0
A couple more options.


Book1
ABCDE
1AlphaNumericUDFAlphaNumeric
2ABC2019ABCDEFGABC2020ABCDEFGABC2020ABCDEFG
3ABC123GHRFDSABC123GHRFDSABC123GHRFDS
Sheet3
Cell Formulas
RangeFormula
C2=RollOver(A2)
C3=RollOver(A3)


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
 
Upvote 0
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>
 
Upvote 0
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?
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top