Warning: strtotime() [function.strtotime]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 35
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 107
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 109
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 111
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 112
Nov
Warning: strtotime() [function.strtotime]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 35
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 107
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 109
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 111
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 112
16
Suppose you have a list consisting of people’s names in a single column. You have to separate these names into three columns: one for the first name, one for the middle name or initial, and one for the last name. This task is more complicated than you may initially think because not every name in the column has a middle name or middle initial. However, you can still do it.
| Note |
The task becomes a lot more complicated if the list contains names with titles (such as Mrs. or Dr.) or names followed by additional details (such as Jr. or III). In fact, the following formulas will not handle these complex cases. However, they still give you a significant head start if you’re willing to do a bit of manual editing to handle the special cases. |
The formulas that follow all assume that the name appears in cell A1.
You can easily construct a formula to return the first name:
=IFERROR(LEFT(A1,FIND(" ",A1)-1),A1)
Returning the middle name or initial is much more complicated because not all names have a middle initial. This formula returns the middle name or initial (if it exists). Otherwise, it returns nothing.
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>1,MID(A1,FIND("
",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-(FIND(" ",A1)+1)),"")
Finally, this formula returns the last name:
=IFERROR(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-
LEN(SUBSTITUTE(A1," ",""))))),"")
The formula that follows is a much shorter way to extract the middle name. This formula is useful if you use the other formulas to extract the first name and the last name. It assumes that the first name is in B1 and the last name is in D1.
=IF(LEN(B1&D1)+2>=LEN(A1),"",MID(A1,LEN(B1)+2,LEN(A1)-LEN(B1&D1)-2)
 |
In many cases, you can eliminate the use of formulas and use Excel’s Data 
Data Tools 
Convert Text to Table command to parse strings into their component parts. Selecting this command displays Excel’s Convert Text to Columns Wizard (see the accompanying figure), which consists of a series of dialog boxes that walk you through the steps to convert a single column of data into multiple columns. Generally, you’ll want to select the Delimited option (in Step 1) and use Space as the delimiter (in Step 2).
|
As you can see in Figure 5-6, the formulas work fairly well. There are a few problems, however-notably names that contain four “words.” But, as I mentioned earlier, you can clean these cases up manually.
Figure 5-6: This worksheet uses formulas to extract the first name, middle name (or initial), and last name from a list of names in column A.
| Cross Ref |
If you want to know how I created these complex formulas, refer to Chapter 20 for a discussion of megaformulas. |
Popularity: 9% [?]
January 22nd, 2009 at 4:32 am
I hope you give John Walkenbach credit for these posts. You plagiarized them directly from the book “Microsoft Office Excel 2007″ ISBN 0-470-04403-9. This one specifically can be found on page 219. And no, I will not buy you a latte for cutting and pasting someone elses work.
April 15th, 2009 at 6:43 pm
My fellow on Orkut shared this link with me and I’m not dissapointed at all that I came here.