Search a Text Sentence for a word(s) from a list

[GKS]

Hi. Can anyone help please. This is doing my head in, I was up till 3 am this morning trying all kinds to get this to work, and yes I have checked similar already answers, so apologies for possible re-posting a same question/issue, but the resolutions posted are not appearing to work in my case.so here goes

We have multiple campains going at the same time. These are recorded in a URL address in raw data. So for instance we have 10 campains running so there are 10 different URL addresses. Each address would only be different only by having the campain name included in the URL for example (leaving out the full url of course.

This is the Unbeatable_prices campaign
=%%&sfmc_e=%%_subscriberkey%%&SFMC_JobId=%%jobid%%#unbeatable-prices

This is the fragrance campaign
.co.uk/shop/beauty/fragrance?productId=48&utm_medium=email&utm_source

This is the make-up campaign
.co.uk/shop/make-up?productId=48&utm_medium=email

These are just snippets of the full address but has the campaign name at different places within the url (text sentence)

These are listed in A column from A2, A3, A4 downwards.

So I have tried making a list of the campaigns in a spare column, in my case its column W
Column W
Row 1 Campaign List
Row 2 fragrance
Row 3 unbeatable
Row 4 make-up
Row 5 Books
Rwo 6 Food
Row 7 Lounge
Row 8 drink

And this list WILL grow as the campaigns come on line.

So How can I say search Cell A2 where the long URL sentence is, for one of the words in the above list and then show that word (campaign) lets say in my case I am working in cell P2. The URL will only show one of the campaigns and not multiple caampaigns in the same URL, so it would only need to find one of the words in the list and report it into cell P2, P3, P4 etc

I have tried
=INDEX(W2:W8,MAX(IF(ISERROR(FIND(W2:W7,A2)),-1,1)*(ROW(W2:W7)-ROW(W2:W7)+1)))
but get #VALUE !

Can anyone help ?????

Thanks

Garry

 

[Aladin Akyurek]

In P2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH($W$1:$W$8,$A2),$W$1:$W$8)

 

[alansidman]

Here is a VBA solution for you.

Option Explicit


Sub FindCampaign()
    Dim i As Long, lrA As Long, lrW As Long, j As Long
    lrA = Range("A" & Rows.Count).End(xlUp).Row
    lrW = Range("W" & Rows.Count).End(xlUp).Row
    For i = 2 To lrW
        For j = 2 To lrA
            If InStr(Range("A" & j), Range("W" & i)) > 0 Then
                Range("P" & j) = Range("W" & i)
            End If
        Next j
    Next i




End Sub

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[GKS]

Thank You so Much Aladin. It has worked a treat. I REALLY appreciate not only the answer, but your time. Thank You.

As I try to learn this formula, Can I ask, why the +307 ? what does that part of the formula do ?

Again Thank You
Garry

 

[Aladin Akyurek]

Thank You so Much Aladin. It has worked a treat. I REALLY appreciate not only the answer, but your time. Thank You.

As I try to learn this formula, Can I ask, why the +307 ? what does that part of the formula do ?

Again Thank You
Garry

E+307 is quantity representing the power to which 9.99999999999999 is raised.

The big number, 9.99999999999999E+307, is a limit value of Excel itself:

See post #5 in: https://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html

Post #3 of the same link describes how it works.

Post #13 of the following link explains how

LOOKUP(BigNum,SEARCH(List,Cell),List) works:

https://www.mrexcel.com/forum/excel-questions/724843-there-case-sensitive-vlookup.html

Hope this helps.

 

[GKS]

Hi Aladin. its been a cpl of days before I have had a chance to reply, so apologies. That's what I love about this site, not only the valuable, indispensable, friendly help, but also not being judged of how little knowledge one might have, or not have in my case, but willing to learn. Thank You for explaining and of course the links, which may actually become very usefull for the next step in this project, I have been given the next instrutions concerning Lookups/search/find and report etc. So of course I am going to give the next stage of this project my best shot, but I may be back on here pleading for help again. Again Thank You for the help with stage 1.

 

[GKS]

Thank You Alan. I do appreciate your help. That sure looks like the knowledge of a very experienced excel user. For curiosity sake I am going to try and follow your instructions and give it a go, it certainly will be a learning curve for me. Thank You

 

[Aladin Akyurek]

@ GKS

You are very welcome.