Find two partial matches in one string

Musiclover

New Member
Joined
Jun 6, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I have gained so much from these forums but find myself stumped on this question.

I have a large data base of file/folder names extracted from multiple folders, about 5000. I want to be able to find two separate partial bits of text in one string and return that string to the next column.

Example:
This is what the extracts look like
F:\Temp list folder\Now Workout\NOW That's What I Call A Workout (2013) CD 1\NOW That's What I Call a Workout 1 - 03 - David Guetta & Usher - Without You (feat. Usher) [Extended].mp3

I want to find if it contains two partial matches, for example match one = Now Workout, match two = Without You (feat. Usher)

Any help anyone can offer would be much appreciated and hopefully allow me a good nights sleep!!!!!

Thank you in advance of your help
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hi MusicLover,

Wildcard searching should work:

MusicLover.xlsx
AB
1F:\Temp list folder\Now Workout\NOW That's What I Call A Workout (2013) CD 1\NOW That's What I Call a Workout 1 - 03 - David Guetta & Usher - Without You (feat. Usher) [Extended].mp3F:\Temp list folder\Now Workout\NOW That's What I Call A Workout (2013) CD 1\NOW That's What I Call a Workout 1 - 03 - David Guetta & Usher - Without You (feat. Usher) [Extended].mp3
2F:\Temp list folder\Without You (feat. Usher)\NOW That's What I Call A Workout (2013) CD 1\NOW That's What I Call a Workout 1 - 03 - David Guetta & Usher - Now WorkoutWithout You (feat. Usher) [Extended].mp3F:\Temp list folder\Without You (feat. Usher)\NOW That's What I Call A Workout (2013) CD 1\NOW That's What I Call a Workout 1 - 03 - David Guetta & Usher - Now WorkoutWithout You (feat. Usher) [Extended].mp3
3Without You (feat. Usher) 
4Now Workout 
5G:\Tempy\Bugbear Blues\Leonard Freemantle 
6F:\Temp list folder\Without You (feat. Usher)\NOW That's What I Call A Workout (2013) CD 1\NOW That's What I Call a Workout 1 - 03 - David Guetta & Usher - Now WorkoutWithout You (feat. Usher) [Extended].mp3F:\Temp list folder\Without You (feat. Usher)\NOW That's What I Call A Workout (2013) CD 1\NOW That's What I Call a Workout 1 - 03 - David Guetta & Usher - Now WorkoutWithout You (feat. Usher) [Extended].mp3
7F:\Temp list folder\\NOW That's What I Call A Workout (2013) CD 1\NOW That's What I Call a Workout 1 - 03 - David Guetta & Usher - Without You (feat. Usher) [Extended].mp3 
8F:\Temp list folder\u (feat. Usher)\NOW That's What I Call A Workout (2013) CD 1\NOW That's What I Call a Workout 1 - 03 - David Guetta & Usher - Now Workout [Extended].mp3 
Sheet1
Cell Formulas
RangeFormula
B1:B8B1=IFERROR(IF(SEARCH("*Now Workout*",A1)+SEARCH("*Without You (feat. Usher)*",A1)=2,A1,""),"")
 
Upvote 0
I may not have asked the question correctly, sorry I am new to the board.

I cannot see how to use your formula, sorry

F:\Temp list folder\Now Workout\NOW That's What I Call A Workout (2013) CD 1\NOW That's What I Call a Workout 1 - 03 - David Guetta & Usher - Without You (feat. Usher) [Extended].mp3
is one of 5000 possible file names and is in column G

Now Workout is a Mid formula and is in F2

Without You (feat. Usher) is a Mid formula and is in H2

Thanks for your prompt reply though
 
Upvote 0
Can you post an example of your data with the desired result?
 
Upvote 0
Thank you Toadstool for your interest, I have found a solution.

=FILTER(Sheet2!$H$1:$H$5500,ISNUMBER(SEARCH(D2,Sheet2!$H$1:$H$5500))*ISNUMBER(SEARCH(E2,Sheet2!$H$1:$H$5500)),”No results”)

Where D2 and E2 are partial matches and H1:H5500 is the list of files and folders.

Hope this helps others
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top