6 Comments

  • Kathleen Lynch says:

    What a timesaver! THANK YOU!!!

  • David May says:

    Great tips, thank you! Another great function for formatting names is CONCATENATE. I use it all the time to merge first and last names into the same field, with a space between them, like so:
    =CONCATENATE(firstnamelocation,” “,lastnamelocation)
    Since it’s a formula, the column this creates (after you’ve Filled Down) won’t paste well into other sheets or columns, so I often “paste special…” and select “values only” to paste just the names and not the formula.

  • tmiket says:

    Hi Ann! It’s even easier in Excel 2013 by using the Flash Fill feature. Just type the first couple and let Excel do the rest without needing any formulas. https://miketips.wordpress.com/2015/09/18/why-write-excel-formulas-when-you-can-flash-fill/

  • George Mount says:

    Love these functions & still surprised how often they’re needed. Many databases will store names in all caps so this is often the first thing I do when converting my data for spreadsheet use.

  • Paul Jamieson says:

    Could you do a user cell with “Lastname, firstname” to individual cells firstname & lastname? our stupid HR app only exports users name in that format and I need to separate them out .. thx

    • Ann K. Emery says:

      Yes!

      If the firstname and lastname are currently together in one column/cell, then you’ll need to split them apart into separate columns/cells first. You can split them apart with either =split or “text to columns.”

      Then, you’ll join them back together as lastname, firstname. You can join contents together with =concatenate (older versions of Excel), =concat (newer versions), or the & shortcut.

      Let me know if you’d like details on any of these.

  • Leave a Reply to George Mount Cancel reply

    Your email address will not be published. Required fields are marked *

    Shortcuts for Formatting Peoples’ Names in Your Excel Spreadsheets

    Updated on: Nov 17th, 2015
    Data Analysis
    , , ,

    Have a spreadsheet full of employee contact information? Demographic data for people in your community? A list of classmates attending that upcoming reunion? Most of our spreadsheets contain peoples’ names at some point in the project lifecycle. Here are a few shortcuts for dealing with text like first and last names.

    Finding the First Initial from Someone’s Name

    There are all sorts of ways to parse out text. In other words, you can have Excel tell you a person’s first initial or last initial.

    In this example, we’ll use the =left() function to find the first initial for Beyonce, Barack Obama, and Pablo Picasso. Type =left( and then click on the cell that contains that person’s first name (e.g., cell A2 which contains Beyonce’s first name). Then, type a comma to move on to the second part of the function. Next, Excel wants to know how many characters (letters or numbers) you’re interested in parsing out. Type a 1 to have Excel give you just the first letter.

    Your completed function in cell C2 will read: =left(A2,1)

    Then, add a closing parentheses to your function and click on the Enter key on your computer’s keyboard to complete the function. Excel will give you a B because B is the left-most letter of Beyonce’s name.

    You’re not limited to just the first letter or number in a cell. You could also type =left(A2,2) into cell D2. Excel will return Be because Be are the first two characters in Beyonce’s name.

    Ann K. Emery on parsing out the first character of a word in Excel

    Switching All Letters to Lowercase

    Sometimes you might need to change all your letters to uppercase or all your letters to lowercase.

    First, let’s have Excel automatically turn some of our cells into lowercase letters. Type =lower( and then click on cell A2, which contains Beyonce’s first name. Add a ) to finish the function and then press Enter on your keyboard.

    In other words, your full function will say =lower(A2)

    Excel will grab the Beyonce text from cell A2 and turn that into beyonce in cell C2.

    Ann K. Emery on switching all letters to lowercase in your spreadsheet

    Switching All Letters to Uppercase

    A related text formatting technique is that Excel enables you to switch all your letters into uppercase letters with just a simple function.

    Type =upper( and click on cell A2 to select Beyonce’s first name. Then, add another parentheses to the end of the function to close it off, and press Enter.

    Excel will transform Beyonce from A2 into BEYONCE in C2.

    Ann K. Emery on switching all letters to uppercase in your spreadsheet

    Proper

    The =proper() function might be the most useful of all: It transforms your messy text into a proper noun format, meaning that the first letter is capitalized and the other letters are lowercase.

    In a perfect world, I would receive spreadsheets that already have this easy-on-the-eyes text formatting. In reality, most of the spreadsheets that I receive from coworkers or download from the internet have a mix of fully lowercase and fully uppercase names.

    Simply type =proper( into cell D2. Then click on the poorly-formatted text you want to transform (like cell C2, which contains Beyonce’s name formatted as BEYONCE). Finally, add a ) to complete the function and press Enter on your keyboard.

    Your final function will look like this: =proper(C2)

    Ann K. Emery on capitalizing proper nouns in Excel

    More about Ann K. Emery
    Ann K. Emery is a sought-after speaker who is determined to get your data out of spreadsheets and into stakeholders’ hands. Each year, she leads more than 100 workshops, webinars, and keynotes for thousands of people around the globe. Her design consultancy also overhauls graphs, publications, and slideshows with the goal of making technical information easier to understand for non-technical audiences.

    6 Comments

  • Kathleen Lynch says:

    What a timesaver! THANK YOU!!!

  • David May says:

    Great tips, thank you! Another great function for formatting names is CONCATENATE. I use it all the time to merge first and last names into the same field, with a space between them, like so:
    =CONCATENATE(firstnamelocation,” “,lastnamelocation)
    Since it’s a formula, the column this creates (after you’ve Filled Down) won’t paste well into other sheets or columns, so I often “paste special…” and select “values only” to paste just the names and not the formula.

  • tmiket says:

    Hi Ann! It’s even easier in Excel 2013 by using the Flash Fill feature. Just type the first couple and let Excel do the rest without needing any formulas. https://miketips.wordpress.com/2015/09/18/why-write-excel-formulas-when-you-can-flash-fill/

  • George Mount says:

    Love these functions & still surprised how often they’re needed. Many databases will store names in all caps so this is often the first thing I do when converting my data for spreadsheet use.

  • Paul Jamieson says:

    Could you do a user cell with “Lastname, firstname” to individual cells firstname & lastname? our stupid HR app only exports users name in that format and I need to separate them out .. thx

    • Ann K. Emery says:

      Yes!

      If the firstname and lastname are currently together in one column/cell, then you’ll need to split them apart into separate columns/cells first. You can split them apart with either =split or “text to columns.”

      Then, you’ll join them back together as lastname, firstname. You can join contents together with =concatenate (older versions of Excel), =concat (newer versions), or the & shortcut.

      Let me know if you’d like details on any of these.

  • Leave a Reply to George Mount Cancel reply

    Your email address will not be published. Required fields are marked *

    You Might Like

    Our complimentary mini course for beginners to dataviz. Takes 45 minutes to complete.

    Enroll

    How to Bring Your Technical Tables to Life

    Just because I’m pro-graph, I’m not anti-table. Technical tables have so much value, especially as visual appendices for reports. In this blog post, you’ll get ideas for bringing your technical tables to life.

    More »

    Need some time-savers in Excel? Feel like all the formulas are taking forever? Learn how to clean, analyze, and tabulate your dataset.

    Enroll

    Subscribe

    Not another fluffy newsletter. Get actionable tips, videos and strategies from Ann in your inbox.