# If cell contains certain text, then return value

#### kalos83

##### New Member
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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Weazel

##### Well-known Member
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
CellFormula
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

##### Board Regular
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

##### Well-known Member
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

##### Well-known Member
Or:

=UPPER(LEFT(A1,2))

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,037
Messages
5,856,962
Members
431,841
Latest member
jaybeem

### 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.

### Which adblocker are you using?

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

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