If cell contains certain text, then return value

[kalos83]

Hi all,

I've got a list of 3 value in a column (It-it_lng, EN-en_lng, DE-DE_lng). The single cell doesn't contain just these values but other value, example: It-it_lng var1, It-it_lng var2, It-it_lng var3)

I need to show in a separate column a value an associated value, for each cell that contain IT-IT_lng, I need to show IT, for each EN-en_lng, EN and for each DE-DE_lng, DE).
This is the desired output.

It-it_lng var1	IT
It-it_lng var2	IT
En-EN_lng var1	EN
De-de_lng var1	DE
It-it_lng var3	IT
de-de_lng var2	DE

<tbody>
</tbody>

I tried something like:

=IF(A1="*IT-IT_lng*","IT","")&IF(A1="*en-en_lng*","EN","")&IF(A1="*de-de_lng*","de","")

I've used "*value*" because I need to get all the cells that contain a value with hundreds of different variations.
But this formula doesn't work. Do you have any guess how can I implement this formula?


Thanks

 

[Weazel]

maybe something like...

Excel 2012

	A	B	C
1	It-it_lng var1	IT	IT
2	It-it_lng var2	IT	IT
3	En-EN_lng var1	EN	EN
4	De-de_lng var1	DE	DE
5	It-it_lng var3	IT	IT
6	de-de_lng var2	DE	DE

<tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula C1 =VLOOKUP(LEFT(A1,FIND(" ",A1)-1),{"It-it_lng","IT";"En-EN_lng","EN";"De-de_lng","DE"},2,0) <tbody> </tbody>

<tbody>
</tbody>

 

[ztodd]

You're on the right track. I did a web search for "excel formula pattern matching"
and found this in another thread on this same site-

(to check if "hi world" is found in the cell)
COUNTIF(A1:A1,"*hi world*")>0

 

[István Hirsch]

Without a formula:

You can achieve this if you Edit/Replace „-??_lng*” with nothing. If you want to keep the original, do this with a copy.

 

[István Hirsch]

Or:

=UPPER(LEFT(A1,2))