Google Sheets is an incredible tool for working with data. You can store, clean, organize, manipulate, analyze, extract from web pages, and download data in various formats.
In this tutorial, we will give you an overview of the text formulas that Google Sheets provides and the most commonly used functions for strings.
Getting Started
Access the sample data by following the link above about New York-based newsrooms, including their addresses, number of social media subscribers, descriptions, and even latitude and longitude.
Make a copy of this file by clicking on File -> Make a copy.
Next, you will see that we have created a new tab at the bottom called “Test here.” This was done so that you have a secure environment to work with your data without erasing the original dataset and having to search for the original file to make another copy.
Always make a copy of the original dataset.
You can either copy and paste the data into the second sheet or duplicate the original file as another sheet.
Grouping Values
Sometimes, you may need to group the values of certain cells for whatever reason you deem necessary. For this, there are two basic formulas – CONCAT for two values and CONCATENATE for more than two values. The application is the same.
In our sample dataset, take a look at the LAT and LONG columns. Some people prefer to have this information in the same column. Let’s do that then.
Create a new column next to the LONG column and apply the formula:
=CONCATENATE(D1, ", ", E1)
We use CONCATENATE because we want to separate the values with a comma – which makes three strings to be grouped. If for any reason you don’t want a comma, you can use CONCAT instead.
Then simply apply the same formula to the other cells by dragging the blue square in the corner of the cell.
You can also use the JOIN formula, which is very similar but allows you to use a specified delimiter.
Separating Values
Just like merging cells, it is also very common that you need to separate values within a cell. There is a basic command for this, the SPLIT function.
You need to determine which character will be used to indicate to your cell to separate. Let’s say you want to undo the CONCAT operation you did and now need to separate LAT and LONG.
First, you split two values, so you will need two additional columns. Create them and apply the formula:
=SPLIT(F1, ", ")
This way, you tell the formula to separate those values in that cell based on the comma you have there. The separation value will disappear.
Find and Replace
Find and replace values can be one of the most useful tools for cleaning and manipulating your data. However, it can also be one of the most dangerous. Be sure to use it with caution.
As you can see in our dataset, the numbers for the number of social media subscribers are separated by commas. This is convenient for making the number clearer, but it can cause problems when using a CSV file or when trying to process the data in charting tools. Let’s remove those commas and make that number “clean.”
Make sure to select only the columns you want to modify. They will be highlighted in blue as shown in the image below. To do this, hold down the SHIFT key and click on the top column headers (the letters).
WARNING: If you fail or forget to do this, you could put all your data in danger, in some cases even other sheet tabs, as you could erase all the commas from that dataset, even those you don’t want to delete.
Then proceed to Edit -> Find and Replace.
The Find is where you insert the character you want to change or remove, in this case, the comma. The Replace with is where you indicate the value you want to add. Since we are cleaning the number to have no separators, we will leave it blank (without spaces). You can add some settings, such as selecting specific ranges in your dataset to modify – but in this case, we want the entire column.
Click the Replace all button.
If nothing happens, it is because the formatting settings of your spreadsheet are automatically adding the commas back because they recognize the values as numbers.
There are different ways to work around this issue.
-
Go to Format -> Number -> Plain Text – this will tell your cell to recognize the content as a string, not a number. Then repeat the find and replace process. This may be a faster approach.
-
Format -> Number -> More formats -> Custom number formats and select a number format without commas or digits. This can tell your entire spreadsheet to read numbers in this way, if desired. This may be a more detailed approach.
TIP: Another approach is to use the SUBSTITUTE function. It is safer and more accurate, but definitely more complex.
Use this function to solve the same problem:
=SUBSTITUTE(H2, ",", "")
This tells that in cell H2, we are replacing the comma with nothing. If you simply want to replace the comma with another character, you can modify the formula accordingly.
Become a Pro
Check out this list of functions and give them a try.
The TRIM function is very useful when you want to extract certain parts of text in a cell or remove empty spaces.
