search for a folder name in file path

[PeterRichter]

Hi fellas, I have file/folder path as showing in column A. I have list of folder names in column C, I like to check if the folder name exist in column C and display the found folder name in column B, can anyone help me to solve this problem? thank you in advance

 

[NdNoviceHlp]

Hi PeterRichter and Welcome to the Board! Do U want a spreadsheet or a VBA solution? Dave

 

[NdNoviceHlp]

I came up with a UDF that seems to work. HTH. Dave
module code...

Option Explicit
Public Function FileName(ShtName As String, SearchCol As String, _
                             SearchAddress As String) As String
'B1 formula =FileName("sheet1","C","A1")
Dim Cnt As Integer, C As Range, TempArr As Variant
Application.Volatile
TempArr = Split(Sheets(ShtName).Range(SearchAddress), "\")
For Cnt = LBound(TempArr) To UBound(TempArr)
Set C = Sheets(ShtName).Range(SearchCol & ":" & SearchCol).Find _
       (What:=TempArr(Cnt), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
If Not C Is Nothing Then
FileName = Sheets(ShtName).Range(SearchCol & C.Row)
Exit Function
End If
Next Cnt
FileName = "NotMatch"
End Function

To operate for sheet1 with file address in "A & row" and keywords in "C" place this formula in "B" & row...

=FileName("sheet1","C","A2")

ie. this formula would go in B2
ps. I'm guessing there's a nifty spreadsheet formula that I won't be able to understand

 

[PeterRichter]

I came up with a UDF that seems to work. HTH. Dave
module code...

Option Explicit
Public Function FileName(ShtName As String, SearchCol As String, _
                             SearchAddress As String) As String
'B1 formula =FileName("sheet1","C","A1")
Dim Cnt As Integer, C As Range, TempArr As Variant
Application.Volatile
TempArr = Split(Sheets(ShtName).Range(SearchAddress), "\")
For Cnt = LBound(TempArr) To UBound(TempArr)
Set C = Sheets(ShtName).Range(SearchCol & ":" & SearchCol).Find _
       (What:=TempArr(Cnt), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
If Not C Is Nothing Then
FileName = Sheets(ShtName).Range(SearchCol & C.Row)
Exit Function
End If
Next Cnt
FileName = "NotMatch"
End Function

To operate for sheet1 with file address in "A & row" and keywords in "C" place this formula in "B" & row...

=FileName("sheet1","C","A2")

ie. this formula would go in B2
ps. I'm guessing there's a nifty spreadsheet formula that I won't be able to understand

thanks so much Dave, I will definitely try the the provided code. Only if you have a min, how would I do this without VBA?

 

[PeterRichter]

thanks so much Dave, I will definitely try the the provided code. Only if you have a min, how would I do this without VBA?

when i copy down the formula (=FileName("sheet1","C","A2") A2 doesn't increment

 

[NdNoviceHlp]

Whoops! That's not so good. This will let U copy down...

Option Explicit
Public Function FileName(ShtName As String, SearchCol As String, _
                             SearchAddress As String) As String
'B2 formula =FileName("sheet1","C",A2)
Dim Cnt As Integer, C As Range, TempArr As Variant
Application.Volatile
TempArr = Split(SearchAddress, "\")
For Cnt = LBound(TempArr) To UBound(TempArr)
Set C = Sheets(ShtName).Range(SearchCol & ":" & SearchCol).Find _
       (What:=TempArr(Cnt), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
If Not C Is Nothing Then
FileName = Sheets(ShtName).Range(SearchCol & C.Row)
Exit Function
End If
Next Cnt
FileName = "NotMatch"
End Function

So "B2" would be this formula...

=FileName("sheet1","C",A2)

which U can copy down. As for a non-VBa solution, we will have to wait for someone more learned than me. HTH. Dave

 

[PeterRichter]

Whoops! That's not so good. This will let U copy down...

Option Explicit
Public Function FileName(ShtName As String, SearchCol As String, _
                             SearchAddress As String) As String
'B2 formula =FileName("sheet1","C",A2)
Dim Cnt As Integer, C As Range, TempArr As Variant
Application.Volatile
TempArr = Split(SearchAddress, "\")
For Cnt = LBound(TempArr) To UBound(TempArr)
Set C = Sheets(ShtName).Range(SearchCol & ":" & SearchCol).Find _
       (What:=TempArr(Cnt), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
If Not C Is Nothing Then
FileName = Sheets(ShtName).Range(SearchCol & C.Row)
Exit Function
End If
Next Cnt
FileName = "NotMatch"
End Function

So "B2" would be this formula...

=FileName("sheet1","C",A2)

which U can copy down. As for a non-VBa solution, we will have to wait for someone more learned than me. HTH. Dave

This one works, thanks buddy

 

[NdNoviceHlp]

You are Welcome. Thanks for posting your outcome. Dave