{"id":7819,"date":"2014-12-15T12:18:39","date_gmt":"2014-12-15T19:18:39","guid":{"rendered":"http:\/\/macblaze.ca\/?p=7819"},"modified":"2014-12-15T12:18:39","modified_gmt":"2014-12-15T19:18:39","slug":"favorite-excel-formulas","status":"publish","type":"post","link":"https:\/\/macblaze.ca\/?p=7819","title":{"rendered":"Favorite Excel Formulas"},"content":{"rendered":"

Since I originally started this blog to help me keep track of coding and computer-related info, I thought I would write down some of my favourite Excel formulas for posterity. Most of these reside in an excel spreadsheet I have carried with me for over a dozen years but here they are appearing on the internet for the the very first time!<\/p>\n

Last Name First Name<\/h4>\n

Simple and prevalent but a lot of people just don’t understand how handy spreadsheets can be for manipulating textual data and do it the manual way.<\/p>\n\n\n\n\n\n
Bill to Name<\/strong><\/td>\nFirst<\/strong><\/td>\nLast<\/strong><\/td>\n<\/tr>\n
Blow, Joe<\/td>\nJoe<\/td>\nBlow<\/td>\n<\/tr>\n
Simmons, Mike<\/td>\nMike<\/td>\nSimmons<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n\n\n\n
Bill to Name<\/strong><\/td>\nFirst<\/strong><\/td>\nLast<\/strong><\/td>\n<\/tr>\n
Blow, Joe<\/td>\n\n

=RIGHT(A2<\/span>,(<\/span>LEN(<\/span>A2<\/span>)<\/span>-SEARCH(<\/span>“,”,A2<\/span>,1)<\/span>)<\/span>-1)<\/p>\n<\/td>\n

\n

=LEFT(A2<\/span>,SEARCH(<\/span>“,”,A2<\/span>,1)<\/span>-1)<\/p>\n<\/td>\n<\/tr>\n

Simmons, Mike<\/td>\n\n

=RIGHT(A3<\/span>,(<\/span>LEN(<\/span>A3<\/span>)<\/span>-SEARCH(<\/span>“,”,A3<\/span>,1)<\/span>)<\/span>-1)<\/p>\n<\/td>\n

\n

=LEFT(A3<\/span>,SEARCH(<\/span>“,”,A3<\/span>,1)<\/span>-1)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

A few things to know about\u00a0calculations<\/p>\n

    \n
  • they all start with the “=” sign.<\/li>\n
  • calculations are often made up of many, nested, formulae.<\/li>\n
  • you refer to specific cells by their coordinates in the grid. “A2” refers to the cell in column A, row 2<\/li>\n
  • spreadsheet calculations\u00a0are hierarchical, just like you learned in grade school. So brackets and nesting brackets are important. The spreadsheet will solve for the deepest level first and then work outwards.<\/li>\n
  • Things in quotes are treated as text; things not in quotes are treated as elements of the formula.<\/li>\n
  • Data elements\u00a0within\u00a0a formula are separated by commas<\/li>\n<\/ul>\n

    So, in the above, you first need to visualize the problem. Essentially I want everything after\u00a0the comma to go in column 2 and everything before the comma to go in column 2. So that means first finding the position of the comma.<\/p>\n

    =SEARCH(“,”,A2<\/span>,1)
    \nsearch (for a comma “,”, in cell A2, and\u00a0look for the first one to appear)<\/p>\n

    The syntax for this formula and all the others that Excel uses can be found by hitting the fx<\/strong> button right by the formula bar\u2014there are a bazillion of them. Once you’ve found the comma you can then grab everything to the right (or left) of it. To do that you need to subtract the position of the comma from the overall length of cell A2.<\/p>\n

    =LEN(<\/span>A20)<\/span>-COMMA POSITION<\/p>\n

    To compensate for the space between the \u00a0comma and the first name you add a -1 \u2014 be sure to remember the brackets because you are subtracting the 1 from the whole calculation.<\/p>\n

    =(LEN(<\/span>A2<\/span>)<\/span>-COMMA POSITION)-1<\/span><\/p>\n

    So now you have the elements necessary to use the right formula whose syntax\u00a0calls is\u00a0Right(text, number of characters).<\/p>\n

    =right(A2, (length-comma position)-1) or
    \n=RIGHT(A2<\/span>,(<\/span>LEN(<\/span>A2<\/span>)<\/span>-SEARCH(<\/span>“,”,A2<\/span>,1)<\/span>)<\/span>-1)<\/p>\n

    To find the last name is almost the same but using the Left\u00a0formula, which has similar syntax to RIGHT. It’s a bit easier because you don’t have to subtract from the length to find the number of characters. You still have to subtract 1 to account for the comma itself.<\/p>\n

    =left(A2,position of the comma-1) or
    \n=LEFT(A32<\/span>,SEARCH(<\/span>“,”,A2<\/span>,1)<\/span>-1)<\/p>\n

    Simple huh? I know, I know, it is complex and mind numbing. That’s why I generally figure\u00a0these calcs once, store them in a master spreadsheet and then reuse them when necessary. Remember that Excel’s fill down command will allow you to repeat the calculation for as many rows as you need and will automatically update the cell numbers to accommodate you.<\/p>\n

    Putting it Back<\/h4>\n

    Changing it back is even easier.<\/p>\n\n\n<\/colgroup>\n\n\n\n
    First<\/strong><\/td>\nLast<\/strong><\/td>\nBill To Name<\/strong><\/td>\n<\/tr>\n
    Frank<\/td>\nJones<\/td>\nJones, Frank<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    =B2\u00a0<\/span>& “, ” & A2<\/span><\/p>\n

    Cell C2 is equal to cell B2 plus a comma and a space (in quotes because they are text not data) and then cell A2. Simple!<\/p>\n","protected":false},"excerpt":{"rendered":"

    Since I originally started this blog to help me keep track of coding and computer-related info, I thought I would write down some of my favourite Excel formulas for posterity. Most of these reside in an excel spreadsheet I have carried with me for over a dozen years but here they are appearing on the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false},"categories":[1],"tags":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/macblaze.ca\/index.php?rest_route=\/wp\/v2\/posts\/7819"}],"collection":[{"href":"https:\/\/macblaze.ca\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/macblaze.ca\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/macblaze.ca\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/macblaze.ca\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7819"}],"version-history":[{"count":0,"href":"https:\/\/macblaze.ca\/index.php?rest_route=\/wp\/v2\/posts\/7819\/revisions"}],"wp:attachment":[{"href":"https:\/\/macblaze.ca\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7819"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/macblaze.ca\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7819"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/macblaze.ca\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7819"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}