5 Excel/Sheets Formulas Every Pro Needs for Data Cleanup

Brittney LaCorte
Dec 29, 2024
2 min read

💼 As an HR professional, I use Excel and Google Sheets every day to manage employee data and reporting. These five essential formulas save me time and make data processing a breeze. Let’s dive in!

🚀 Must-Know Formulas for HR and Compensation

1. LEFT() & RIGHT() – Extract Key Data

I apply this formula every day to retrieve job levels or department codes from our employee database. The left formula pulls data from the beginning (or left side), while the right formula extracts from the end (or right side).

LEFT(): Extracts characters from the start of a string.
Formula: =LEFT(A2, 2)
Example: "HR001" → "HR"
RIGHT(): Grabs characters from the end of a string.
Formula: =RIGHT(A3, 4)
Example: "EMP2022" → "2022"

2. Use & to Combine Data

Combine first and last names into one cell.

Formula: =A2 & " " & B2
Example:
- First Name: "John"
- Last Name: "Doe"
Output: "John Doe"

3. SPLIT() – Separate Data

Separate combined data into individual columns. In Excel, you can utilize the "Text to Columns tool" for this purpose.

Formula: =SPLIT(A2,", ")
Example:
- "Doe, John" → "Doe" (in one cell) and "John" (in another).

4. TRIM() – Clean Up Messy Data

Remove unwanted spaces in text. 💡 Expert Tip: Apply this formula to eliminate hidden spaces that are disrupting the functionality of your other formulas.

Formula: =TRIM(F2)
Example:
- Input: " Alice Johnson "
- Output: "Alice Johnson"

5. IF() – Simplify Decision-Making

If statements are fantastic, and I will explore them further in another post. For now, this formula can evaluate conditions such as salary limits or bonus qualification. When creating it, consider "If X occurs, then what should the cell display, and if it doesn't, what should it show".