10 Google Sheets Functions for Text Data Cleaning and Transformation

I used to think Google Sheets or Excel are some old-fashion tools and probably Python and R sounds more fancier than these until I join my current company. In start-ups environment, things are usually quite manual and there comes tons of Spreadsheets to keep track of the data.

Today, I am going to share with you the 10 Basic Spreadsheets Text Formulas with some examples. Whether you're a marketer or business owner, or just anyone who wants to get insights from data, it's important to learn how to perform some simple text transformation using spreadsheets. This is useful for quick analysis and cleaning up data before importing it to Business Intelligence Tools like Google Data Studio.

All the examples are available in the video below.

Text Functions in Google Sheets

These functions can be divided into 4 main groups:

  • Text Capitalization
  • Text Extraction
  • Text Transformation
  • Meta Function

Text Capitalization

It's rare to receive clean data in real world scenarios, especially when the source is coming from user inputs and worse if it's a free text field. Text Capitalization is one of the useful ways to standardise string values in spreadsheets.

LOWER

LOWER() function is used to convert text into lowercases.

UPPER

UPPER() function can be used to convert all the text characters to uppercase.

PROPER

PROPER() formula is used to improve readability by only capitalising the initial letter of each word in a string, the rest of the letters would be converted to lowercase. I personally like to use this as it looks more professional and neat for management report or dashboard.

Text Extraction

These functions can be used to extract characters or text based on the position of the characters in Google Sheets cells. I used these functions quite often when I need to fix inconsistent date formats.

LEFT

A useful formula to extract characters from the most left using their position within the text.

RIGHT

A useful formula to extract characters from the most right using their position within the text. 

MID

MID function is slightly less straightforward compared to LEFT/RIGHT formula, but basically you just need to indicate the position of the character you want to start with, follow by the number of character you wish to get. Then, MAGIC happens! ✨

Text Transformation

SPLIT

SPLIT formula is very useful when you have more than 1 value combined in a cell. You can define the delimiter and split the string/text into multiple cells.

CONCATENATE 

With CONCATENATE function, you can combine or merge two or more columns into one.

SUBSTITUTE

When you have just one specific character or word to replace, you can use SUBSTITUTE function. For example, the word “take” here is misspelled, and i can correct it easily to “take it easy” instead of “cake it easy” using SUBSTITUTE function.

Meta Function

LEN

A useful formula to count the total characters in a cell. Note that if your cell contains spaces and symbol, it'll also be counted as one of the characters.

Fun Examples for Google Sheets Text Formula

For quick examples for all the text formulas above, please refer the video below.

 

Back to blog