For freelancers in administrative roles such as executive assistants, or project managers, your job may include data entry into spreadsheets, and creating reports using tools like Excel and Google Sheets.
It's important to understand and be able to use some Excel functions that manipulate data to improve your efficiency and speed.
Apart from the mathematical functions, some Excel functions I've found really handy are the Text functions. These functions are powerful tools designed to manipulate and format text strings within your spreadsheets. They save you time and effort by automating tasks that would otherwise require manual editing.
In this article, I'll discuss some of the most commonly used Excel text functions that make data manipulation a breeze!
CONCATENATE
The first function we'll look at, and one of my favorites is the CONCATENATE function.Concatenate joins two or more text strings into one string.
The Syntax for Concatenate:
=CONCATENATE(A2, " ", B2)
In this formula, A2 represents the column with the first string, the double quotation mark with space in-between (" ") indicates that there should be a space in-between the first string and second string after joining, and B2 represents the column with the second string.
Use Case:
Combining first name and last name into a full name.
Using the formula, we can quickly join first names in column A with last names in column B into full names of individuals in a record without having to 'copy' and 'paste'.
LEFT, RIGHT, and MID
Next are the LEFT, RIGHT, and MID functions.
These functions extract a specified number of characters from a text string, starting from the left, right, or a specified position.
LEFT Function:
The Syntax for LEFT function:
=LEFT(C2, 3):
In this formula, C2 represents the column name where the text string is, and 3 represents the number of characters we want returned from our text string.
Using the names in our concatenated column (Column C), we have extracted the first 3 characters from the left into another column.
RIGHT Function:
The Syntax for RIGHT function:
=RIGHT(C2, 6):
In this formula, C2 represents the column name where the text string is, and 6 represents the number of characters we want returned from our text string.
Also, using the names in our concatenated column (Column C), we have extracted the last 6 characters from the right into another column.
MID Function:
The Syntax for MID function:
=MID(C2, 5, 3):
In this formula, C2 represents the column name where the text string is,5 represents where we want the extraction to start, and 3 represents the number of characters we want returned from our text string.
With the MID function, We have extracted 3 characters from the text in cell C2 and C3, starting from the 5th character.
Other Useful Text Functions:
1. LEN: Returns the number of characters in a text string.
2. FIND and SEARCH: Find the position of a substring within a text string.
3. SUBSTITUTE: Replaces occurrences of a specified text string within another text string.
4. UPPER, LOWER, and PROPER: Converts text to upper, lower, or proper cases.
5. TRIM: Removes extra spaces from text, leaving only single spaces between words.
If you do a lot of data entry using Excel, particularly text data, create comprehensive reports, track project timelines, or analyze financial data, mastering these functions can significantly enhance your productivity and accuracy.
Start integrating these powerful tools into your workflow and experience a boost in productivity and precision.