Check if text exists in a cell, then write that text in another cell

[Legacy 330023]

Hi,

I have a formula request

I have a list of words in column (A), sometimes severeal words in one cell, and another column with swedish citys (C). Now I´m looking for a formula in cell B1 that check if A1 contains any of the citys in C column and if it does, write that citys name in B1. If it doesn´t leave it blank or write False or something.

So greatful for your help!

Thnx
Fabian

 

[aromaboards]

Hi,

I have a formula request

I have a list of words in column (A), sometimes severeal words in one cell, and another column with swedish citys (C). Now I´m looking for a formula in cell B1 that check if A1 contains any of the citys in C column and if it does, write that citys name in B1. If it doesn´t leave it blank or write False or something.

So greatful for your help!

Thnx
Fabian

So if I understand this correct, your coulmn A has Words, names or sentences, Example A1= "i want to go to Halmstad " and what your looking for is a formula to look at the Text and if any part of that text contains the same as any name in C Column, then display the city from C column in Cell B1.?

Is that right?

 

[Legacy 330023]

So if I understand this correct, your coulmn A has Words, names or sentences, Example A1= "i want to go to Halmstad " and what your looking for is a formula to look at the Text and if any part of that text contains the same as any name in C Column, then display the city from C column in Cell B1.?

Is that right?

I think you got it exactly right. I have tried other forums but they couldn´t solve it.

/Fabian

 

[Legacy 330023]

Example...

	A	B	C
1	i like halmstad	halmstad	arboga
2	stores in stockholm	stockholm	stockholm
3	stockholm stores	stockholm	halmstad
4	nice stores	"blank"	göteborg
5	i like you	"blank	malmö

<tbody>
</tbody>

The result of the formula is in column B

 

[aromaboards]

I dont have much down time, off hand i dont think theres a formula that you can input into the cell, possibly run a macro? Closest thing i got on my break was =SUMPRODUCT(COUNTIF(A2,"*"&$B$2:$B$11&"*"))>0 but this only gives true or false values, and in my test file it was giving some false positives. I will post again in in a day or two if I figured something out.

 

[XOR LX]

Hi.

In B1:

=IFERROR(LOOKUP(1,0/SEARCH(C$1:C$5,A1),C$1:C$5),"")

Copy down as required.

Note that this set-up does nothing to account for potential cases where a search term forms part of a word: for example, if A1 contained "I like darbogast", then this formula would return "arboga".

If such situations are possible in your set-up, then we will to use a more complex set-up.

Regards

 

[Legacy 330023]

Allright. Thanks anyway! Hope we´ll find a solution
Take care,

/Fabian

 

[Legacy 330023]

Wow I think we´re close!
As you describe, I discovered that that situiation is possible.
Is there a way to attach or send a file to you?

Otherwise I´ll try to show it below.
There are two citys named Köping and Enköping. It looks like it chooses the name Köping in both cases.

ansiktsbehandling enköping	köping	abbekås
ansiktsbehandling köping	köping	göteborg
		enköping
		köping
		akalla
		alberga
		alby
		stockholm
		tuna
		eskilstuna

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[XOR LX]

Will there ever be any punctuation marks within the strings in column A?

Regards

 

[Legacy 330023]

Nope. Just plain text.