Extract account number from file path name I have a list of file path names, and

ID: 640339 • Letter: E

Question

Extract account number from file path name

I have a list of file path names, and a list of account numbers. I need to extract the account number from the file path name if it contains a value from the account number list.

File Path:

\HHops.ok.company.comajd$AmericJustsEntry_0022395773

\HHops.ol.business.comf44556$AmericGoalsDEFAULT_660-00884020y3

\HHops.ok.company.comasdawd$AmericTracksDEFAULT_1

\HHops.ok.company.comfhjd$AmericGoals_009-XT983HG39877

\HHops.ok.legos.xmlfhjd$AmericStopsBackingUp00038[AT556ET90284]

Account Numbers:

0022395773

00884020y3

XT983HG39877

AT556ET90284

For example, for the first file path, I would need Excel to return 0022395773 since the account number is listed in the account number table.

Let me know if you have any questions!

Thanks you !

Explanation / Answer

Hi..

Yes, those numbers suggest a different approach.

Try this for a first attempt. It uses the Find method to locate any instances of Account_Number in your list of URL's. The results are written to an array, rather than the worksheet. After the array is fully populated, it is then written to the worksheet in a single step.

I did NOT assume that the account numbers would only match a single URL. If you can guarantee that there will only be a single match, we can eliminate some lines of code, which would speed up the macro.

If this is still too slow, then we will need to explore doing rapid searches completely within VBA arrays.

The macro assumes

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), select the sheet with the list of URLs. <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

===================================

Option Explicit
Sub MatchAccountNums()
    Dim rAN As Range, vAN As Variant
    Dim rURL As Range, vURL As Variant
    Dim I As Long, J As Long
    Dim rAcct As Range
    Dim sFirstAddress As String

Set rURL = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Resize(columnsize:=2)
    rURL.Columns(2).Clear
Set rAN = Range("Account_Numbers");

vAN = rAN
vURL = rURL

    For J = 1 To UBound(vAN)
        Set rAcct = rURL.Find(what:=vAN(J, 1), _
            LookIn:=xlValues, lookat:=xlPart, _
            MatchCase:=False)
        If Not rAcct Is Nothing Then .
            vURL(rAcct.Row, 2) = vAN(J, 1)
            sFirstAddress = rAcct.Address
            Do
                Set rAcct = rURL.FindNext(rAcct)
                If sFirstAddress <> rAcct.Address Then _
                    vURL(rAcct.Row, 2) = vAN(J, 1)
            Loop Until rAcct.Address = sFirstAddress
        End If
    Next J


Application.ScreenUpdating = False
With rURL
    .EntireColumn.Clear
    .NumberFormat = "@"
    .Value = vURL
    .EntireColumn.AutoFit
End With
Application.ScreenUpdating = True
End Sub

Navigate

Extract 1 Acid Whey: The Less Delicious Byproduct Of The Greek Yogurt Boom Greek

Extract information from graph below and solve for power, specific heat capacity

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

Extract account number from file path name I have a list of file path names, and

Question

Explanation / Answer

Related Questions

Navigate