In the professional world, tools like Google Sheets and Excel are commonly used to track and analyze data. Today, I’m going to share with you 10 essential functions for cleaning and transforming text data in spreadsheets. Whether you’re a marketer, a business owner, or someone who simply wants to derive insights from data, it’s important to learn how to perform simple text transformations using spreadsheets. This skill is tremendously useful for quick analysis and data cleaning before importing it into business intelligence tools like Google Data Studio.
Text Capitalization
It’s rare to receive clean data in real-life scenarios, especially when the source comes from users, and even more so if it’s a free text field. Text capitalization is one useful way to standardize string values in spreadsheets.
LOWER
The LOWER() function is used to convert text to lowercase.
UPPER
The UPPER() function can be used to convert all characters in the text to uppercase.
PROPER
The PROPER() formula is used to improve readability by capitalizing only the first letter of each word in a string, with the rest of the letters converted to lowercase. Personally, I find this visually appealing as it gives a more professional and polished look to management reports or dashboards.
Text Extraction
These functions can be used to extract characters or text based on their position within Google Sheets cells. I often use these functions when I need to fix inconsistent date formats.
LEFT
A handy formula for extracting the leftmost characters based on their position in the text.
RIGHT
A useful formula for extracting the rightmost characters based on their position in the text.
MID
The MID function is slightly less straightforward than the LEFT/RIGHT formulas, but essentially, you just need to specify the position of the character you want to start with, followed by the number of characters you want to obtain. Then, the magic happens! ✨
Text Transformation
SPLIT
The SPLIT formula is very useful when you have more than one combined value in a single cell. You can define the delimiter and split the string into multiple cells.
CONCATENATE
With the CONCATENATE function, you can combine or merge two or more columns into one.
SUBSTITUTE
When you have a specific character or word to replace, you can use the SUBSTITUTE function. For example, the word “prendre” here is misspelled, and I can easily correct it to “prendre du bon temps” instead of “gâteau du bon temps” using the SUBSTITUTE function.
Meta Function
LEN
A useful formula for counting the total number of characters in a cell. Note that if your cell contains spaces and symbols, they will also be counted as characters.
Fun Examples of Text Functions in Google Sheets
For quick examples of all the text formulas mentioned above, please refer to the video below.
Now that you’re armed with these 10 useful functions to clean and transform text data in Google Sheets, you can leverage them to enhance your analysis and reports. Remember to practice and familiarize yourself with these functions to fully master them. And if you want to learn more about using Google Sheets and other online marketing tools, visit Crawlan.com for valuable tips and tricks.
