Find which of multiple Unique Text Strings are present within a Longer Text String

[britz]

Im using Excel 2007 & Windows 7. I do not have any programing or VBA experience.

I have managed to be able to find when the "Unique Text String" is present in a "Longer Text String" by using the function =SEARCH(TRIM(D3),TRIM(A3)).
The "Longer Text String" varies in length & sometimes contains extra spaces, hence the need for the Trim operation.

The issue I have, is that I now want to be able to search each "Longer Text String" & identify which of the "Unique Text Strings" is present, then tell me the corresponding related No.
If it were only 5 "Unique Text Strings", then it would be easy to create 5 columns, one for each of the "Unique Text Strings" to the right & customise the formula for each "Unique Test String" in each column.

The tricky bit is there are over 60 "Unique Text Strings" & thousands of "Longer Text Strings". So I would like to do this in a lot less than 60 separate functions.
I hope you can give me some ideas?

Longer Text Strings		No.	Unique Test String
The Brown Fox in the box 1		1	Brown Fox
The Black Cat on the mat		2	Black Cat
The Bird in the Tree		3	Tree
The Snake on the Grass		4	Snake
The man on the moon		5	Moon
The Brown Owl on the Barn
The Dog fighting the Cat
The Small Tree in the Garden
The Brown Fox in the box 2

<tbody>
</tbody>

 

[britz]

Hi Hiker95,

Thanks again for your time & your clear instructions.
Ive not previously run macro code other than the basic record function.
I followed your instructions to a tee & to my surprise, this code worked like a charm. Really cool how multiple results appear int he one cell where applicable.

Thanks again for your time & Interest.

Regards,

Britz

 

[hiker95]

britz,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[Akashwani]

Hi Britz,

I misunderstood your requirement, I assumed you wanted a count of all the Unique Strings within the longer string!

You have been provided with an excellent solution by Hiker95, but thanks for the feedback.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Longer Text Strings	Single String Result		No.	Unique Test String		# Uniq Strings
2	The Brown Fox in the box 1	1		1	Brown Fox		1
3	The Black Cat on the mat	7		2	Black Cat		3
4	The Bird in the Tree	3		3	Tree		1
5	The Snake on the Grass	7		4	Snake		2
6	The man on the moon	7		5	Moon		2
7	The Brown Owl on the Barn	7		6	Cat		1
8	The Dog fighting the Cat	6		7	On		1
9	The Small Tree in the Garden	3					1
10	The Brown Fox in the box 2	1					1
11
Sheet2

Ak