Google Sheets Tutorial: Text functions for your data

Welcome to this Google Sheets tutorial where we will explore the basic text functions. In this post, you will learn how to work with functions like =LEFT, =RIGHT, =MID, =LEN, and other text functions to effectively analyze and edit your data. With the help of these functions, you can easily extract text from a string, determine the length of text, and concatenate values. The tutorial is based on a practical example from a financial company where we work with account numbers and customer names. Let's get started!

Key Takeaways

• With the =LEFT function, you can extract strings at the beginning of a given string.
• The =RIGHT function allows you to extract strings from the end of the string.
• The =LEN function indicates the number of characters in a string.
• The =MID function lets you extract certain parts of a string.
• Using the =CONCATENATE function, you can merge different text parts.

Step-by-Step Guide

Using the =LEFT function

First, let's look at the =LEFT function. This function allows you to return a part of a string at the beginning of a given string. To apply the function, type "=LEFT(" in the cell, followed by the cell reference you want to analyze. For example, if you have the account number in cell E3 and want to extract the first five characters, the formula looks like this:

=LEFT(E3; 5)

In this case, the text "Schmidt" will be displayed in the first cell. The goal is to extract the account number. You can simply drag this formula down to apply it to the other cells.

Applying the =RIGHT function

The =RIGHT function is the counterpart to the =LEFT function, where you extract strings from the end. In our example, we want to return the last name "Schmidt" from the right end. You use the function similarly:

=RIGHT(E3; 7)

In this case, the formula returns the last seven characters of the name. But what if the last name is shorter? This is a common issue when applying this function to other data.

Understanding the =LEN function

To solve the issue of different last name lengths, we use the =LEN function. This function indicates the number of characters in a string. The end of the formula looks like this:

=LEN(E3)

With this calculation, you can determine the length of each name and ensure that the correct number of characters is read when dragging the =RIGHT function.

Combining Functions for Better Results

Now, we combine the insights from the =LEN function with the =RIGHT function. You can say: "I want to start from the right in cell E3 and count the number of characters according to the length minus a certain number." Here is the formula:

=RIGHT(E3; LEN(E3)-6)

This formula is applied to all relevant cells and immediately displays the correct last name, regardless of the number of characters.

Using the =MID Function

Another valuable component of text functions is the =MID function. With this function, you can extract parts of a string based on a starting point and a number of characters. For example, to extract starting from the third character for five characters, the formula is:

=MID(E3; 3; 5)

You can adjust this formula and also drag it down for multiple cells.

Using the =CONCATENATE Function

If you want to display the syntax and formulas you have in a cell in a clear way, you can use the =FORMULATEXT function. This way you can see at a glance how the =PART function looks in another cell:

=FORMULATEXT(G3)

This allows you to check formulas without double-clicking.

Handling Data Types with the =VALUE Function

In Google Sheets, it is important to use the correct data type. The =VALUE function helps you convert text into numbers. For example, you can:

=VALUE(E3)

Here the combination of numbers and texts is correctly interpreted as a number.

Using the =CONCATENATE Function

If you want to concatenate different text parts, you can use the =CONCATENATE function. For example, you can combine an account number and a customer's name like this:

=CONCATENATE(A3; " "; B3)

The content from the account number is merged with the name to be displayed in a single cell.

Summary

In this lesson, you have learned the basic text functions in Google Sheets that will help you analyze your data more effectively. You have learned how to manipulate strings by using the =LEFT-, =RIGHT-, =PART-, =LENGTH-, and =CONCATENATE functions, as well as by using the =FORMULATEXT and =VALUE functions. Now you are able to efficiently manage your data and extract specific information from your text data.