Find two partial matches in one string

Musiclover

New Member
Joined
Jun 6, 2020
Messages
5
Office Version
365
Platform
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
710
Office Version
2016
Platform
Windows
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].mp3
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
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].mp3
F:\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].mp3
F:\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,""),"")
 

Musiclover

New Member
Joined
Jun 6, 2020
Messages
5
Office Version
365
Platform
Windows
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
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
710
Office Version
2016
Platform
Windows
Can you post an example of your data with the desired result?
 

Musiclover

New Member
Joined
Jun 6, 2020
Messages
5
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,052
Messages
5,466,284
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top