Extracting partial data from a cell

[momodog]

I extracted some data and its very ugly... I have over 200 rows of data and some cell with 20 different countries, and all I want is the percentage for a certain country say Mexico.

In cell B1, I have a row that looks like this
Brazil: 9.1%France: 8.2%Mexico: 3.1%

In cell B2
Italy: 25.6%Mexico: 13.3%Brazil: 12.5%India: 4.5%

So all I want from B1 is 3.1% say in A1, and 13.3% in A2.

Thanks

 

[JuanPa]

momodog:
Have a look at this:

=MID(A1,FIND("Mexico: ",A1,1)+LEN("Mexico: ")-1,4)

The function MID extracts information from the middle of a cell. Its three arguments are:
1. Text, the cell where the information is located;
2. Start, the position where to start extracting. In this case, I'm using the nested function FIND, please see below;
3. Length, the number of characters to be extracted. Taking into the percentage is one-digit, plus point, plus one decimal, plus percentage sign =4.

The function FIND, pretty much loooks for the starting point where a string is located inside other. Its (three, again) arguments are:
1. Searched text: What the looked for expression is;
2. Inside text: the cell where the data is;
3. Starting position, i.e. 1.

Finally, function LEN calculates how many characters long a string is. I this this in case you want to make a reference to another cell, so just replace "Mexico: " for the cell where the desired country is.
Regards,
JuanPa

 

[Haseeb Avarakkan]

Try,

D2, Enter the Country name.
Copy down the formula.

If the lookup country is not in the current row, will show as 0.

Format the cell to percentage.


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">3.1%</td><td style=";">Brazil: 9.1%France: 8.2%Mexico: 3.1%</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Lookup Country</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">13.3%</td><td style=";">Italy: 25.6%Mexico: 13.3%Brazil: 12.5%India: 4.5%</td><td style="text-align: right;;"></td><td style="text-align: center;;">Mexico</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A1</th><td style="text-align:left">=LOOKUP(<font color="Blue">1E+100,CHOOSE(<font color="Red">{1,2},0,LOOKUP(<font color="Green">1E+100,--LEFT(<font color="Purple">REPLACE(<font color="Teal">B1,1,SEARCH(<font color="#FF00FF">$D$2,B1</font>)+LEN(<font color="#FF00FF">$D$2</font>),""</font>),{1,2,3,4,5,6}</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[momodog]

momodog:
Have a look at this:

=MID(A1,FIND("Mexico: ",A1,1)+LEN("Mexico: ")-1,4)

The function MID extracts information from the middle of a cell. Its three arguments are:
1. Text, the cell where the information is located;
2. Start, the position where to start extracting. In this case, I'm using the nested function FIND, please see below;
3. Length, the number of characters to be extracted. Taking into the percentage is one-digit, plus point, plus one decimal, plus percentage sign =4.

The function FIND, pretty much loooks for the starting point where a string is located inside other. Its (three, again) arguments are:
1. Searched text: What the looked for expression is;
2. Inside text: the cell where the data is;
3. Starting position, i.e. 1.

Finally, function LEN calculates how many characters long a string is. I this this in case you want to make a reference to another cell, so just replace "Mexico: " for the cell where the desired country is.
Regards,
JuanPa

Thanks you!!!

 

[momodog]

Try,

D2, Enter the Country name.
Copy down the formula.

If the lookup country is not in the current row, will show as 0.

Format the cell to percentage.


Excel 2007<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(224, 224, 240);" width="25px"><col><col><col><col></colgroup><thead><tr style="background-color: rgb(224, 224, 240); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">1</td><td style="text-align: right;">3.1%</td><td style="">Brazil: 9.1%France: 8.2%Mexico: 3.1%</td><td style="text-align: right;">
</td><td style="font-weight: bold;">Lookup Country</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">2</td><td style="text-align: right;">13.3%</td><td style="">Italy: 25.6%Mexico: 13.3%Brazil: 12.5%India: 4.5%</td><td style="text-align: right;">
</td><td style="text-align: center;">Mexico</td></tr></tbody></table>

Sheet1​

<table style="border: 2px solid black; border-collapse: collapse; padding: 0.4em; background-color: rgb(255, 255, 255);" width="85%" cellpadding="2.5px" rules="all"><tbody><tr><td style="padding: 6px;">Worksheet Formulas<table style="border: 1px solid rgb(166, 170, 182); text-align: center; background-color: rgb(255, 255, 255); border-collapse: collapse;" width="100%" cellpadding="2.5px" rules="all"><thead><tr style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);"><th width="10px">Cell</th><th style="text-align: left; padding-left: 5px;">Formula</th></tr></thead><tbody><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">A1</th><td style="text-align: left;">=LOOKUP(1E+100,CHOOSE({1,2},0,LOOKUP(1E+100,--LEFT(REPLACE(B1,1,SEARCH($D$2,B1)+LEN($D$2),""),{1,2,3,4,5,6}))))</td></tr></tbody></table></td></tr></tbody></table>

Thanks Haseeb, I noticed I had to manually update some cells with the other formula, but I don't need to with yours.

Thank you!!!