VBA help - Rollforward date within text

[DaniNaj]

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]

Is the year the only numeric value within the text?

If so, try this:

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

 

[DanteAmor]

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

 

[lrobbo314]

A couple more options.

Book1
	A	B	C	D	E
1	AlphaNumeric		UDF		AlphaNumeric
2	ABC2019ABCDEFG		ABC2020ABCDEFG		ABC2020ABCDEFG
3	ABC123GHRFDS		ABC123GHRFDS		ABC123GHRFDS
Sheet3

Cell Formulas
Range	Formula
C2	=RollOver(A2)
C3	=RollOver(A3)

UDF 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

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]

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]

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]

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]

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]

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.

 

[DaniNaj]

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.

Sorry for the duplication; and Thank you Peter