but only when all the cells have the same number of characters.
If you want to separate employees' names or emails you can't use LEFT, RIGHT and MID by themselves because all the names and emails are different lengths. This is where the SEARCH command comes in.
In the example below, I want to separate my first name from my last name so to do that I need to combine the LEFT function and the Search function.
The actual function is =LEFT(A2,Search(" ",A2)-1.Excel searches A2 for a space as denoted by the empty quotation marks, in the formula, and finds it between my first and last name so at this point the formula would read as =Left(A2, 9-1)
So, what does that mean? Excel searched and it found the empty space. This empty space is the 9th character. We subtract 1 to tell Excel we only want 8 characters. The LEFT function then retrieves the 8 characters to the left in cell A2 and returns my name Patricia.
That was the easy one. Retrieving the last name is a little bit more work. In this case, I am using MID and SEARCH.In case you are not familiar with MID, the syntax is =MID(cell reference, start_number, number of characters)
The formula is =MID(A2,SEARCH(" ",A2)+1,35).
Excel reads that as =MID(A2,10,35)
To break the formula down, I again told it to search for the empty space between the first and last name and to use that as the starting point. Then, I told it to go over 1 character and return the next 35 characters. I arbitrarily picked 35 since few last names would have more than 35 characters.
Below are examples, if you wanted to pull apart an email address.
We finally have enough fans to have our own Facebook URL. If you have a chance, check it out and click on the LIKE button while you are there. If you Facebook, all of these blog entries also appear there as do my tweets. It is like one -stop shopping!
I am watching tiny snow flakes coming down as I type this. Have a good week and stay warm.
No comments:
Post a Comment