Parsing
Excel has numerous functions for parsing data. Frequently, clients will call me because they need to rearrange data in Excel or extract only certain information from a cell. Let's say a client would like to take the first and last name of their list and put the first name into column B. This is easily accomplished using functions like left & right. In our example, cell A2 will contain the name John Doe.
To get only the first name in cell B2, we can enter the following formula.
=LEFT(A2,FIND(" ",A2)-1)
If you entered the formula exactly as it appears above, you should see the name John in cell B2. The first parameter sets the string that we would like to extract data from, in our example, we are specify cell A2, or John Doe as our string. The second parameter set the number of characters starting from the Left that we would like to return. Since we know first and last name are typically separated by a space, we use the Find function to determine where the space is in the string. We do not wish to return the name and the extra space (although we wouldn't notice it), so we subtract 1 from the Find function.
For the next lesson, we'll put up a post on how to extract the last name using the Right function.