HR Excel Skills (Part 1) - Working with Names in Excel
The ability to manipulate and utilize excel is one of the underrated skills of HR professionals. The ability to pull reports and data are the basics of the function. The key to being successful with excel is knowing the correct formulas to use and how to use them.
Let's take one of the common scenarios that a HR professional faces, working with names. Often times HR is given a list of names in an excel sheet but in order to pull or display the information they want, they have to use a formula or two.
For example, let's say the names in a spreadsheet appear as last name comma first name. But you need the names to be displayed first name last name. You can easily make this change using a series of simple formulas in excel.
Download the Working with Names spreadsheet to see how this is done. Below are some useful formulas that will help you manipulate and work with names in Excel.
Trim
Trim removes the extra spaces that appear before and/or after a text string. This allows you to more easily work with the text by removing the hidden spaces in the text string.
Example:
FedEx Sara Evans (Original)
FedEx Sara Evans ( =Trim(A1))
Substitute
Substitute replaces text in a string with the text of your choosing.
Example:
FedEx Sara Evans (Original)
Sara Evans ( =Substitute(A1,"Fedex",""))
Left
This function will give you a string of text beginning with the left-most of the string.
Example:
Sara Evans (Original)
Sara ( =Left(A1,4))
Find
Find identifies where a character or part of a text appears in a text string.
Example:
Sara Evans (Original)
5 =(Find(" ",A1)) The space in Sara Evans is the 5th character in the text.
Combining Find and Left
By combining Find and Left you can automatically isolate the first name or first part of a text string.
Example:
Sara Evans (Original)
Sara ( =Left(A1,Find(" ",A1,-1))
For more on how to work with names in Excel download our file.
