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?


[TABLE="width: 500"]
<tbody>[TR]
[TD]Longer Text Strings
[/TD]
[TD][/TD]
[TD]No.
[/TD]
[TD]Unique Test String
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]The Brown Fox in the box 1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]Brown Fox
[/TD]
[/TR]
[TR]
[TD]The Black Cat on the mat
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]Black Cat
[/TD]
[/TR]
[TR]
[TD]The Bird in the Tree
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]Tree
[/TD]
[/TR]
[TR]
[TD]The Snake on the Grass
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]Snake
[/TD]
[/TR]
[TR]
[TD]The man on the moon
[/TD]
[TD][/TD]
[TD]5
[/TD]
[TD]Moon
[/TD]
[/TR]
[TR]
[TD]The Brown Owl on the Barn
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]The Dog fighting the Cat
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]The Small Tree in the Garden
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]The Brown Fox in the box 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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