Manipulating data in excel is a big part of what and many others end up doing on a daily basis. How can I most easily manipulate large sets of data I will take an example of something I had to do recently for a project of mine. I had a set of 200 tennis players in the following format:
I then needed to extract from this data a few informations in order to populate a table with these columns:
First and Last Name
Country
Points
You might think that it would be easy to do but how would you do it really? Remember that the current data has the last name in front rather than their first names. First off, I needed to separate each part of the first column. How? By using the “find” function. In fact, I need to find out for each line:
-Where the ranking ends (how many digits)
-Where the first and last names start and how logn they are
-Where to find the country
How will I do so? I will simply use the “find” function. First off, to find the “rank”, I will use:
=FIND(” “,A2)
The result for the first line is 2 meaning that the first space is in the 2nd character.
Then, I will determine the end of the last name by doing this:
=FIND(“,”,A2)
Then, I find the end of the first name by doing this:
=FIND(“(“,A2,D2+2)
I am using a 3rd range in this function, where I can specify where to start looking for this space. With this, I should be able to gather all of the information that I need.
Rank =LEFT(A2,C2-1)
In order to find the first name, I will be using the “mid” function. Basically, it helps me get a part of that text, starting at a point I determine, for a length that I also determine. In this case, I would get:
First Name = MID(A2,D2+2,E2-D2-2)
Last Name =MID(A2,C2+1,D2-C2-1)
Finally, to find the country, I will use the excel function “len” which returns the length of a cell. Why? I will simply do a mid that will start a bit past that last space and continue until the end of the cell (minus 1) just in case some country symbols have more or less letters than 3.
Country =MID(A2,E2+1,LEN(A2)-E2-1)
Here is the end result:
Obviously, you might find it easier to see things in action, you can download the spreadsheet here
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
