Testfile and pairs of numbers

[jonh]

Hi,

I'm trying to convert data from an old to a new payroll system. One field contains multiple pairs of dates (start & finish, yy format) as follows:

Col A
Row1 89~92~95~97
Row2 ~~~90~94~
Row3 ~~81~90~~98~03~~~

The problem I have is that the new system needs dates in dd/mm/yy format, therefore, to import the above example it would have to appear as follows:

01/01/89~31/12/92~01/01/95~31/12/97
~~~01/01/90~31/12/94~
~~01/01/81~31/12/90~~01/01/98~31/12/03~~~

I need to maintain all the ~ (tilde) and put 01/01/ in front of the first number of the pair and 31/12/ in front of the second number in the pair. Unfortunately, there are over 20,000 entries in column A.

Has anyone any idea of how I would overcome this problem?

 

[GlennUK]

How many number pairs per line?

Are there any rules about whereabouts in the string the numbers will appear?

 

[jonh]

Hi Gleen,

Unfortunately there can be multiple pairs, but the max is 5. If 5 then the string is as follows:

81~87~87~88~88~89~93~93~95~96

Otherwise, the pair(s) can appear anywhere in the string i.e.
88~94~94~97
~86~~87
74~79

Regards,
Jon

 

[GlennUK]

Yeuch. I think this'll need VBA. Am tied up right now. If no-one else comes up with a solution I may have time to code you something on Sunday.

 

[jonh]

Thanks for your help!

 

[facethegod]

Try this VBA UDF
1.Copy below code.
2.In Excel press Alt + F11 to enter the VBE.
3.Press Ctrl + R to show the Project Explorer.
4.Right-click desired file on left (in bold).
5.Choose Insert -> Module.
6.Paste code into the right pane.
7.Press Alt + Q to close the VBE.
8.Type in the funtion as a normal excel function( etc. =convert_pairs(A1))

Function convert_pairs(s As String) As String
Dim re As Object, m As Object, mm, i As Integer, x As Integer, ss As String
Set re = CreateObject("VBScript.RegExp")
With re: .Global = True: .Pattern = "\d{2}"
If .Test(s) Then
Set m = .Execute(s): s = "~" & s
For Each mm In m
i = i + 1: x = i Mod 2
s = Application.Substitute(s, "~" & mm, IIf(x = 1, "~01/01/" & mm, "~31/12/" & mm), 1)
Next
End If
convert_pairs = Right(s, Len(s) - 1)
End With
End Function

 

[jonh]

Thank you for the code below, it has saved it an enormous amount of time. Perhaps when you have time, you could explain the code in detail.

All the best!

 

[jonh]

Try this VBA UDF
1.Copy below code.
2.In Excel press Alt + F11 to enter the VBE.
3.Press Ctrl + R to show the Project Explorer.
4.Right-click desired file on left (in bold).
5.Choose Insert -> Module.
6.Paste code into the right pane.
7.Press Alt + Q to close the VBE.
8.Type in the funtion as a normal excel function( etc. =convert_pairs(A1))

Function convert_pairs(s As String) As String
Dim re As Object, m As Object, mm, i As Integer, x As Integer, ss As String
Set re = CreateObject("VBScript.RegExp")
With re: .Global = True: .Pattern = "\d{2}"
If .Test(s) Then
Set m = .Execute(s): s = "~" & s
For Each mm In m
i = i + 1: x = i Mod 2
s = Application.Substitute(s, "~" & mm, IIf(x = 1, "~01/01/" & mm, "~31/12/" & mm), 1)
Next
End If
convert_pairs = Right(s, Len(s) - 1)
End With
End Function

Thanks for the above function. It works on about 75% of the data, however, when the dates in YY format are not in sequential order i.e.
96~97~97~01~01~02~02~04, the function returns the following:
31/12/01/01/96~31/12/97~01/01/01/01/97~01~01~31/12/02~01/01/02~31/12/04.
It seems that not having the data in sequential order detorts the result. Is it possible to fix this?

Thanks once again.

 

[facethegod]

try

Option Explicit
Function convert_pairs(s As String) As String
Dim re As Object, m As Object, mm, i As Integer, x As Integer, ss As String
Set re = CreateObject("VBScript.RegExp")
With re: .Global = True: .Pattern = "\d{2}"
If .Test(s) Then
Set m = .Execute(s): s = "~" & s & "~"
For Each mm In m
i = i + 1: x = i Mod 2
s = Application.Substitute(s, "~" & mm & "~", IIf(x = 1, "~01/01/" & mm & "~", "~31/12/" & mm & "~"), 1)
Next
End If
convert_pairs = Left(Right(s, Len(s) - 1), Len(s) - 2)
End With
End Function

 

[pgc01]

Hi

Another option:

Function Convert_pairs(s As String) As String
 
With CreateObject("VBScript.RegExp")
    .Pattern = "(\d\d)~(\d\d)"
    .Global = True
    Convert_pairs = .Replace(s, "01/01/$1~31/12/$2")
End With
End Function