Hi!

I tried to search previous posts for this, but honestly couldn't figure what search terms to use ...

My payroll system spits out a file each day that has a horrendous format .... lots of merged cells and such. I'm trying to build code that prepares this data file for use in a tracking template, the first step of which is to parse the co-worker key (name & employee #). And, it's this first step that has me stuck.

The data (co-worker name & #) starts in J19, with 53 rows btw each subsequent entry. The format for this entry is John Doe (12345678). The number should be 8 digits but isn't always; it *is* always between the ( ).

Can you help me with coding to isolate just the employee # and put in the cell next to the original ... and do this until the next cell (53 rows below) is blank?

Thanks in advance!

Ray

Hi Ray,

Am Wed, 8 Apr 2015 05:20:43 -0700 (PDT) schrieb Ray:

The data (co-worker name & #) starts in J19, with 53 rows btw each subsequent entry. The format for this entry is John Doe (12345678). The number should be 8 digits but isn't always; it *is* always between the ( ).

column K must be empty. If not, then insert a new column.

Sub Test()
Dim varData As Variant, varOut() As Variant
Dim LRow As Long, i As Long, n As Long

With ActiveSheet
LRow = .Cells(Rows.Count, "J").End(xlUp).Row
ReDim Preserve varOut(LRow - 1, 1)
varData = .Range("J19:J" & LRow)
For i = 1 To UBound(varData)
If InStr(varData(i, 1), "(") Then
varOut(n, 0) = WorksheetFunction.Trim(Mid(varData(i, 1), _
1, InStr(varData(i, 1), "(") - 1))
varOut(n, 1) =
WorksheetFunction.Trim(Replace(Replace(Replace(var Data(i, 1), _
varOut(n, 0), ""), "(", ""), ")", ""))
Else
varOut(n, 0) = ""
varOut(n, 1) = ""
End If
n = n + 1
Next
.Range("J19").Resize(n, 2) = varOut
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus -

Thanks so much, works perfectly! Now I'll kill the rest of my morning dissecting the code to figure out how it works ... ;)

/ray

Hi Ray,

Am Wed, 8 Apr 2015 06:01:50 -0700 (PDT) schrieb Ray:

Thanks so much, works perfectly! Now I'll kill the rest of my morning dissecting the code to figure out how it works ... ;)

you are welcome.
You also can do it with Regular Expressions:

Sub Test()
Dim LRow As Long, i As Long
Dim varData1 As Variant, varData2 As Variant
Dim ptrn1 As String, ptrn2 As String
Dim re As Object

'Set a reference to Microsoft VBScript Regular Expressions
With ActiveSheet
LRow = .Cells(Rows.Count, "J").End(xlUp).Row
varData1 = .Range("J19:J" & LRow)
varData2 = .Range("J19:J" & LRow)

ptrn1 = "\d|\(|\)"
ptrn2 = "\D"

Set re = New RegExp
re.Pattern = ptrn1
re.IgnoreCase = False
re.Global = True

For i = 1 To UBound(varData1)
varData1(i, 1) = WorksheetFunction.Trim(re.Replace(varData1(i,
1), ""))
Next

re.Pattern = ptrn2
re.IgnoreCase = False
re.Global = True

For i = 1 To UBound(varData2)
varData2(i, 1) = WorksheetFunction.Trim(re.Replace(varData2(i,
1), ""))
Next

.Range("J19").Resize(UBound(varData1)) = varData1
.Range("K19").Resize(UBound(varData2)) = varData2
End With

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Il giorno mercoledì 8 aprile 2015 14:20:51 UTC+2, Ray ha scritto:
I tried to search previous posts for this, but honestly couldn't figure what search terms to use ...
My payroll system spits out a file each day that has a horrendous format .... lots of merged cells and such. I'm trying to build code that prepares this data file for use in a tracking template, the first step of which is to parse the co-worker key (name & employee #). And, it's this first step that has me stuck.
The data (co-worker name & #) starts in J19, with 53 rows btw each subsequent entry. The format for this entry is John Doe (12345678). The number should be 8 digits but isn't always; it *is* always between the ( ).
Can you help me with coding to isolate just the employee # and put in the cell next to the original ... and do this until the next cell (53 rows below) is blank?

Hi Ray,
try:

Public Sub Test1()

' --- CUSTOMIZE ----------
'
Const cstrSheet As String = "Sheet1"
Const cstrRange As String = "J19"
Const cstrFormat As String = "00000000"
'
' --- CUSTOMIZE ---------- <

Dim wbk As Excel.Workbook
Dim wsh As Excel.Worksheet
Dim rng As Excel.Range
Dim r As Long
Dim s As String
Dim p1 As Long
Dim p2 As Long

Set wbk = Application.ThisWorkbook
Set wsh = wbk.Worksheets(cstrSheet)
Set rng = wsh.Range(cstrRange)

'r = 0: s = ""
Do
s = rng.Offset(r).Value
If Len(s) = 0 Then Exit Do
p1 = InStr(s, "(")
p2 = InStr(s, ")")
rng.Offset(r, 1).Value = "'" _
& Right$(cstrFormat _
& Mid$(s, p1 + 1, p2 - p1 - 1) _
, Len(cstrFormat))
r = r + 1
Loop

Set rng = Nothing
Set wsh = Nothing
Set wbk = Nothing

End Sub

--
Ciao! :)
Maurizio