If cell contains certain text, then return value

kalos83

New Member
Joined
Sep 25, 2014
Messages
1
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 var1IT
It-it_lng var2IT
En-EN_lng var1EN
De-de_lng var1DE
It-it_lng var3IT
de-de_lng var2DE

<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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
maybe something like...

Excel 2012
ABC
1It-it_lng var1ITIT
2It-it_lng var2ITIT
3En-EN_lng var1ENEN
4De-de_lng var1DEDE
5It-it_lng var3ITIT
6de-de_lng var2DEDE

<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
Joined
Sep 17, 2014
Messages
221
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
Joined
May 16, 2013
Messages
1,634
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,545
Members
414,316
Latest member
ExcelLee

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top