Searching for a "text" in a long text cell

[zaducu]

Hello guys and girls,

I just arrived on this forum and I think it is the best Excel community I've discovered until now. I am currently having an Excel issue. I have a table with mentions of a specific car brand from social media channels. I have to analyse the discussions and show the number of time a specific model of the car has been mentioned in all the mentions that are collected.

Below you can see more exactly what I want to achieve.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Brand mentions[/TD]
[TD]Model A90[/TD]
[TD]Model B60[/TD]
[TD]Model C30[/TD]
[/TR]
[TR]
[TD]Lorem ipsum A90[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Lorem ipsum a 90 text text[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]A 90 more text more text[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]asdfa B 60 asdas asd[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]b 60 text text text[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]sad C30 bag[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]asdasd c30.[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]asdas dadsda sda[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

There are 2 issues I don't know how to solve:

• The text from the first coloumn is including the value but not alone. So COUNTIF or VLOOKUP didn't helped me, or I didn't knew how to set them.
• I want to search for more than one value (so not only search for A90 but search for "A90" AND "A 90" AND "a90" AND "a 90")

I would really appreciate your help. I hope I was clear.

Thank you!
Alex

 

[JLeno]

I put the following formula in cell B2, then copied to the entire area:

=IF(ISERROR(SEARCH(RIGHT(B$1,3),SUBSTITUTE($A2," ",""))),0,1)

 

[zaducu]

Hello JLeno, thank you very much for your answer. It used your formula but it seems that it has some issue. For example I have the model ABC2000 but it gives value 1 to all the text which include 2000, so for example "When i first got the car 20 000 road" is marked as 1.

 

[JLeno]

The formula searches for the last three characters of the top row (e.g. A90, B90, etc.) So if there is a ABC2000 it searches for all texts that contain "000".

I would suggest you include an extra row with just the model number, without "Model" in front of it, in row 2. Then change the formula to

=IF(ISERROR(SEARCH(B$2,SUBSTITUTE($A3," ",""))),0,1)

 

[zaducu]

The problem is that some models don't follow the pattern so their format are either "PV832" OR "Word Word" OR "WORD WORD C40". Do you know if there are any other solutions? Thank you very much for your help and time.

 

[JLeno]

Well if you have put the model names on the separate row, you can amend the formula like this:

=IF(AND(ISERROR(SEARCH(B$2,SUBSTITUTE($A3," ",""))),ISERROR(SEARCH(B$2,$A3))),0,1)

 

[zaducu]

Well if you have put the model names on the separate row, you can amend the formula like this:

=IF(AND(ISERROR(SEARCH(B$2,SUBSTITUTE($A3," ",""))),ISERROR(SEARCH(B$2,$A3))),0,1)

I've eliminated the car model which included only words so now I have models like C60, C360, xc90. But I need to keep the letters because there are models like C60 and XC60. So if I would leave only 60 it will take both models.

 

[JLeno]

That's fine. I think it should work with C60 and XC60