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

britz

New Member
Joined
Apr 7, 2013
Messages
5
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]
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
britz,

Welcome to the MrExcel forum.

How about a very fast macro solution using arrays in memory? The macro compares lower case Unique Test String's against lower case Longer Text Strings.

Sample raw data:


Excel 2007
ABCDE
1Longer Text StringsNo.Unique Test String
2The Brown Fox in the box 11Brown Fox
3The Black Cat on the mat2Black Cat
4The Bird in the Tree3Tree
5The Snake on the Grass4Snake
6The man on the moon5Moon
7The Brown Owl on the Barn
8The Dog fighting the Cat
9The Small Tree in the Garden
10The Brown Fox in the box 2
11
Sheet1


After the macro:


Excel 2007
ABCDE
1Longer Text StringsNo.Unique Test String
2The Brown Fox in the box 11Brown Fox2
3The Black Cat on the mat2Black Cat1
4The Bird in the Tree3Tree2
5The Snake on the Grass4Snake1
6The man on the moon5Moon1
7The Brown Owl on the Barn
8The Dog fighting the Cat
9The Small Tree in the Garden
10The Brown Fox in the box 2
11
Sheet1


If you added another Unique Test String:


Excel 2007
ABCDE
1Longer Text StringsNo.Unique Test String
2The Brown Fox in the box 11Brown Fox2
3The Black Cat on the mat2Black Cat1
4The Bird in the Tree3Tree2
5The Snake on the Grass4Snake1
6The man on the moon5Moon1
7The Brown Owl on the Barn6on
8The Dog fighting the Cat
9The Small Tree in the Garden
10The Brown Fox in the box 2
11
Sheet1


And, ran the macro again:


Excel 2007
ABCDE
1Longer Text StringsNo.Unique Test String
2The Brown Fox in the box 11Brown Fox2
3The Black Cat on the mat2Black Cat1
4The Bird in the Tree3Tree2
5The Snake on the Grass4Snake1
6The man on the moon5Moon1
7The Brown Owl on the Barn6on4
8The Dog fighting the Cat
9The Small Tree in the Garden
10The Brown Fox in the box 2
11
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Option Explicit
Sub CountUniques()
' hiker95, 04/10/2013
' http://www.mrexcel.com/forum/excel-questions/696092-find-multiple-unique-text-strings-present-within-longer-text-string.html
Dim a As Variant, d As Variant
Dim lr As Long, i As Long, ii As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
a = Range("A2:A" & lr)
lr = Cells(Rows.Count, 4).End(xlUp).Row
Range("E2:E" & lr).ClearContents
d = Range("D2:E" & lr)
For i = 1 To UBound(a, 1)
  For ii = 1 To UBound(d, 1)
    If InStr(LCase(a(i, 1)), LCase(d(ii, 1))) > 0 Then
      d(ii, 2) = d(ii, 2) + 1
    End If
  Next ii
Next i
Range("D2").Resize(UBound(d, 1), UBound(d, 2)) = d
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CountUniques macro.
 
Last edited:
Upvote 0
Hi hiker95,

Thank you for taking time to read & answer my query.

My error in not clarifying. I would like the result in Column B for the Search in each "Longer Text String" in Column A.


Thank you & Regards,

Britz
 
Upvote 0
Hi Britz and welcome to MrExcel.

Is this what you require?...

Sample data...

Excel Workbook
ABCDE
1Longer Text StringsResultsNo.Unique Test String*
2The Brown Fox in the box 111Brown Fox*
3The Black Cat on the mat32Black Cat*
4The Bird in the Tree13Tree*
5The Snake on the Grass24Snake*
6The man on the moon25Moon*
7The Brown Owl on the Barn16Cat*
8The Dog fighting the Cat17On*
9The Small Tree in the Garden1***
10The Brown Fox in the box 21***
11*****
Sheet2


The formula in B2 needs to be copied down.
UniqString is a Named Range based on the data in Column D
To create a Named Range...
Highlight your range, right click, click Name a Range, type in a name like UniqString into the white box, click OK.

I hope this helps.

Ak
 
Upvote 0
Also how about


Excel 2010
ABCDE
1Longer Text StringsRequired ResultNo.Unique Test String
2The Brown Fox in the box 111Brown Fox
3The Black Cat on the mat22Black Cat
4The Bird in the Tree33Tree
5The Snake on the Grass44Snake
6The man on the moon55Moon
7The Brown Owl on the Barn76Dog
8The Dog fighting the Cat67Owl
9The Small Tree in the Garden3
10The Brown Fox in the box 21
Sheet1
Cell Formulas
RangeFormula
B2=LOOKUP(9.99E+307,SEARCH($E$2:$E$8,A2),$D$2:$D$8)
 
Upvote 0
britz,

My error in not clarifying. I would like the result in Column B for the Search in each "Longer Text String" in Column A.

Thank you.

Sample raw data:


Excel 2007
ABCD
1Longer Text StringsNo.Unique Test String
2The Brown Fox in the box 11Brown Fox
3The Black Cat on the mat2Black Cat
4The Bird in the Tree3Tree
5The Snake on the Grass4Snake
6The man on the moon5Moon
7The Brown Owl on the Barn
8The Dog fighting the Cat
9The Small Tree in the Garden
10The Brown Fox in the box 2
11
Sheet1


After the macro:


Excel 2007
ABCD
1Longer Text StringsNo.Unique Test String
2The Brown Fox in the box 111Brown Fox
3The Black Cat on the mat22Black Cat
4The Bird in the Tree33Tree
5The Snake on the Grass44Snake
6The man on the moon55Moon
7The Brown Owl on the Barn
8The Dog fighting the Cat
9The Small Tree in the Garden3
10The Brown Fox in the box 21
11
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:
Option Explicit
Sub CountUniquesV2()
' hiker95, 04/11/2013
' http://www.mrexcel.com/forum/excel-questions/696092-find-multiple-unique-text-strings-present-within-longer-text-string.html
Dim a As Variant, b As Variant, d As Variant
Dim lr As Long, i As Long, ii As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
a = Range("A2:A" & lr)
lr = Cells(Rows.Count, 4).End(xlUp).Row
Range("E2:E" & lr).ClearContents
d = Range("C2:D" & lr)
ReDim b(1 To UBound(a, 1), 1 To 1)
For i = 1 To UBound(a, 1)
  For ii = 1 To UBound(d, 1)
    If InStr(LCase(a(i, 1)), LCase(d(ii, 2))) > 0 Then
      b(i, 1) = d(ii, 1)
    End If
  Next ii
Next i
Range("B2").Resize(UBound(b, 1)) = b
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the CountUniquesV2 macro.
 
Upvote 0
britz,

If we started with this:


Excel 2007
ABCD
1Longer Text StringsNo.Unique Test String
2The Brown Fox in the box 11Brown Fox
3The Black Cat on the mat2Black Cat
4The Bird in the Tree3Tree
5The Snake on the Grass4Snake
6The man on the moon5Moon
7The Brown Owl on the Barn
8The Dog fighting the Cat
9The Small Tree in the Garden
10The Brown Fox in the box 2
11
Sheet1


And ran the below newest macro we would get this:


Excel 2007
ABCD
1Longer Text StringsNo.Unique Test String
2The Brown Fox in the box 111Brown Fox
3The Black Cat on the mat22Black Cat
4The Bird in the Tree33Tree
5The Snake on the Grass44Snake
6The man on the moon55Moon
7The Brown Owl on the Barn
8The Dog fighting the Cat
9The Small Tree in the Garden3
10The Brown Fox in the box 21
11
Sheet1


If you added #6, on to the list like this:


Excel 2007
ABCD
1Longer Text StringsNo.Unique Test String
2The Brown Fox in the box 111Brown Fox
3The Black Cat on the mat22Black Cat
4The Bird in the Tree33Tree
5The Snake on the Grass44Snake
6The man on the moon55Moon
7The Brown Owl on the Barn6on
8The Dog fighting the Cat
9The Small Tree in the Garden3
10The Brown Fox in the box 21
11
Sheet1


And, ran the macro again, you would get this:


Excel 2007
ABCD
1Longer Text StringsNo.Unique Test String
2The Brown Fox in the box 111Brown Fox
3The Black Cat on the mat2,62Black Cat
4The Bird in the Tree33Tree
5The Snake on the Grass4,64Snake
6The man on the moon5,65Moon
7The Brown Owl on the Barn66on
8The Dog fighting the Cat
9The Small Tree in the Garden3
10The Brown Fox in the box 21
11
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:
Option Explicit
Sub CountUniquesV3()
' hiker95, 04/11/2013
' http://www.mrexcel.com/forum/excel-questions/696092-find-multiple-unique-text-strings-present-within-longer-text-string.html
Dim a As Variant, b As Variant, d As Variant
Dim lr As Long, i As Long, ii As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
a = Range("A2:A" & lr)
lr = Cells(Rows.Count, 4).End(xlUp).Row
Range("E2:E" & lr).ClearContents
d = Range("C2:D" & lr)
ReDim b(1 To UBound(a, 1), 1 To 1)
For i = 1 To UBound(a, 1)
  For ii = 1 To UBound(d, 1)
    If InStr(LCase(a(i, 1)), LCase(d(ii, 2))) > 0 Then
      If b(i, 1) = "" Then
        b(i, 1) = d(ii, 1)
      Else
        b(i, 1) = b(i, 1) & "," & d(ii, 1)
      End If
    End If
  Next ii
Next i
Range("B2").Resize(UBound(b, 1)) = b
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CountUniquesV3 macro.
 
Upvote 0
Hi AK,

Thanks for this.
Ive tried this on my real data set & not the sample set in this post, so im not sure if there is a hitch with my data set?
It appears to pick-up some of the Unique Test Strings, but all others result in Number ones in Column B, when they should be otherwise.

Thanks for your time & interest.

Regards,
Britz
 
Upvote 0
Hi Sandeep,

Thanks for this one. Wow, It works correctly on my real data set. I dint think I was going to get a working solution.
Only thing is that the result is the actual "Unique Text String". How would I return the Adjacent Number as per Column D in the above example?

Thank you & Kind Regards

Britz
 
Upvote 0
britz,

How would I return the Adjacent Number as per Column D in the above example?

Sample raw data (per the above quote):


Excel 2007
ABCDE
1Longer Text StringsNo.Unique Test String
2The Brown Fox in the box 11Brown Fox
3The Black Cat on the mat2Black Cat
4The Bird in the Tree3Tree
5The Snake on the Grass4Snake
6The man on the moon5Moon
7The Brown Owl on the Barn
8The Dog fighting the Cat
9The Small Tree in the Garden
10The Brown Fox in the box 2
11
Sheet1


After the below posted macro:


Excel 2007
ABCDE
1Longer Text StringsNo.Unique Test String
2The Brown Fox in the box 111Brown Fox
3The Black Cat on the mat22Black Cat
4The Bird in the Tree33Tree
5The Snake on the Grass44Snake
6The man on the moon55Moon
7The Brown Owl on the Barn
8The Dog fighting the Cat
9The Small Tree in the Garden3
10The Brown Fox in the box 21
11
Sheet1


If you added item #6, like this:


Excel 2007
ABCDE
1Longer Text StringsNo.Unique Test String
2The Brown Fox in the box 111Brown Fox
3The Black Cat on the mat22Black Cat
4The Bird in the Tree33Tree
5The Snake on the Grass44Snake
6The man on the moon55Moon
7The Brown Owl on the Barn6on
8The Dog fighting the Cat
9The Small Tree in the Garden3
10The Brown Fox in the box 21
11
Sheet1


And, ran the macro again, you would get this:


Excel 2007
ABCDE
1Longer Text StringsNo.Unique Test String
2The Brown Fox in the box 111Brown Fox
3The Black Cat on the mat2,62Black Cat
4The Bird in the Tree33Tree
5The Snake on the Grass4,64Snake
6The man on the moon5,65Moon
7The Brown Owl on the Barn66on
8The Dog fighting the Cat
9The Small Tree in the Garden3
10The Brown Fox in the box 21
11
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:
Option Explicit
Sub CountUniquesV4()
' hiker95, 04/11/2013
' http://www.mrexcel.com/forum/excel-questions/696092-find-multiple-unique-text-strings-present-within-longer-text-string.html
Dim a As Variant, b As Variant, d As Variant
Dim lr As Long, i As Long, ii As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
a = Range("A2:A" & lr)
Range("B2:B" & lr).ClearContents
lr = Cells(Rows.Count, 4).End(xlUp).Row
d = Range("D2:E" & lr)
ReDim b(1 To UBound(a, 1), 1 To 1)
For i = 1 To UBound(a, 1)
  For ii = 1 To UBound(d, 1)
    If InStr(LCase(a(i, 1)), LCase(d(ii, 2))) > 0 Then
      If b(i, 1) = "" Then
        b(i, 1) = d(ii, 1)
      Else
        b(i, 1) = b(i, 1) & "," & d(ii, 1)
      End If
    End If
  Next ii
Next i
Range("B2").Resize(UBound(b, 1)) = b
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CountUniquesV4 macro.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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