On Feb 8, 5:17 am, "Tawreq" <taw...@gmail.comwrote:
Hi All,
I have quite a unique issue - I would be very grateful if someone
could help out with this challenge....
I have two columns, Column A contains and acronym, and Column B
contains a corresponding file path. Like below:
Column A
FSSC
GPM SBU
AL ACT
....
Column B
/_op_sox/Project/Default/ICDocumentation/Controls/LSSB/ITO/GPM/FSSC/
ADM-E-R001-C001.txt
/_op_sox/Project/Default/ICDocumentation/Controls/LSSB/ITO/GPM/GPM
SBU/
ADM-I-R003-R001.txt
/_op_sox/Project/Default/ICDocumentation/Controls/LSSB/II/AL/AL ACT/
ALV-A-R001-C001.txt
...
The 9th folder in the file path matches the acronym in column A, but
only 90% of the time. I have around 3000 rows and I need to highlight
which ones don't match the acronym in column A. Basically, what I
want
to do, is chop off everything before 9th "\" and everything after the
10th "\" and see if it matches column A - can anyone solve this
challenge?
Thank you so much!
Regards,
Tariq
I don't have much time to spend on it, but here's one that works with
at least 2 "/" above the file. You'll have to modify it if you need to
allow for any path (not just 9 folder in) - and if a different
delimiter is used ( \ instead of /). It will currently fail if the
path is not in the same format as you posted.
After modifying it to meet your needs, this should always give you the
last listed folder (probably better than relying on it to always be
the 9th). Use it in a qry and compare the output with ColumnA.
Function GetLastFolder(pth As String) As String
On Error GoTo stoprun
GetLastFolder = ""
Dim a As Long
Dim pos As Long
Dim slshLocs() As Long
Dim lenFldr As Long
a = 0
pos = 1
Do Until pos = 0
pos = InStr(pos, pth, "/")
If pos <0 Then
ReDim Preserve slshLocs(a)
slshLocs(a) = pos
a = a + 1
pos = pos + 1
End If
Loop
If UBound(slshLocs) >= 1 Then
a = UBound(slshLocs)
pos = UBound(slshLocs) - 1
lenFldr = slshLocs(a) - slshLocs(pos) - 1
GetLastFolder = Mid(pth, slshLocs(pos) + 1, lenFldr)
End If
exit_here:
Exit Function
stoprun:
MsgBox Err.Number & " - " & Err.Description
Resume exit_here
End Function
