Tags

, , ,

I’ve just extracted some list of countries that were put in one dropdown menu. I got them from the source code so it looks something like that:

<option label="Afghanistan" value="4">Afghanistan</option>
<option label="Albania" value="5">Albania</option>
<option label="Algeria" value="6">Algeria</option>
<option label="American Samoa" value="7">American Samoa</option>
<option label="Andorra" value="8">Andorra</option>
<option label="Angola" value="9">Angola</option>
.....................................................

What I want to do is extract just the names of the countries. To do that I copy all these lines into a new excel spreadsheet.

To start with I want to get rid of the regular expression:

<option label="

To do this I simply use the replace command (Ctrl+H) and replace the above expression with nothing.

Having done that I end up with a list like this:

Afghanistan" value="4">Afghanistan</option>
Albania" value="5">Albania</option>
Algeria" value="6">Algeria</option>
American Samoa" value="7">American Samoa</option>
Andorra" value="8">Andorra</option>
Angola" value="9">Angola</option>
...............................................

So that was pretty easy. Now I want to get rid of the part which is to the right from the  word value. To do that I use the following formula:

 =LEFT(A1,LEN(A1)-FIND("value",A1)-18)

By applying this formula to all countries I end up with a list just the way I want:

Afghanistan
Albania
Algeria
American Samoa
Andorra
Angola
......................................

This formula is not perfect and might not work for you perfectly from the first time you use it but it’s pretty powerful and you can easily manipulate it. For instance it works perfectly with listing many file and removing the file extensions.

Advertisements