asked on

Finding percentage a keyword mentions in an Excel file

I have a very large MS Excel file which I imported from a pdf.

The data looks like this for the most part (the | indicates the start of a new column):

<ID>   |   <Description>   |   <Address Line 1>
                                                    |   <Address Line 2>
                                                    |   <Address Line 3>


<ID>   |   <Description>    |   <Address Line 1>
                                          |   <Address Line 2>
                                          |   <Address Line 3>
                                          |     <Address Line 4>


<ID>   |   <Description>   |   <Address Line 1>
                                         |    <Address Line 2>


<ID>   |   <Description>   |   <Address Line 1>
<ID2> |  <Description2>  |    <Address Line 2>

Open in new window

The address lines sometimes span multiple rows and other times enlarge the height of the cell to accommodate the text. I need to extract the country from wherever it is in the Address Lines and then calculate the percentage of the total countries in the spreadsheet. Because the data isn't entirely clean, I'm wondering if this is possible. Even if it isn't 100% exact, even an approximation will still be useful for me.

Martin Liss

Do you have or could you create a list of countries. Given such a list it would be a fairly easy task to create a macro to 'Find', each country, record the number of hits and then perform the percentage calculation. Can you attach a sample workbook?

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.