LEFT and RIGHT Functions in Google Sheets for Data Extraction

Published on

The LEFT and RIGHT functions help extract substrings from the start or end of a specified string in Google Sheets.

These functions are useful for regular tasks such as separating first and last names when combined with SEARCH or FIND.

Let’s learn about these two text functions one by one and their combined use with SEARCH and FIND.

LEFT Function in Google Sheets

Syntax:

LEFT(string, [number_of_characters])

Arguments:

  • string: The string from which to extract the left portion at the specified number_of_characters.
  • number_of_characters: The number of characters to return. The default value is 1. If you specify 0, the formula will return an empty string.

Examples:

=LEFT("Info Inspired", 4) // returns "Info"
=LEFT("Info Inspired", 0) // returns ""

Combined Use of LEFT and SEARCH or FIND

The SEARCH and FIND functions are useful for finding the position of a specific character or substring in a text. The former is case-insensitive, whereas the latter is case-sensitive.

Below, I’ll demonstrate the combination of the LEFT and SEARCH functions in Google Sheets. Remember, you can replace SEARCH with FIND.

Assume you want to extract the first name from cell A1. You need to search for the first space character in cell A1 and use that position as the number of characters to return in LEFT.

=LEFT(A1, SEARCH(" ", A1)-1)

If cell A1 contains “Ashley Holmes”, the formula will return the first name “Ashley”. The formula subtracts 1 from the SEARCH position to avoid including the space character in the result.

RIGHT Function in Google Sheets

Syntax:

RIGHT(string, [number_of_characters])

Arguments:

  • string: The string from which to extract the right portion.
  • number_of_characters: The number of characters to extract, 1 by default. If you specify 0, the formula will return an empty string.

Examples:

=RIGHT("Info Inspired", 8) // returns "Inspired"
=RIGHT("Info Inspired", 0) // returns ""

Combined Use of RIGHT, LEN, and SEARCH or FIND

You can use a combination of LEN and SEARCH or FIND functions with the RIGHT function to dynamically extract a substring from a string.

For example, to extract the last name from the name in cell A1, use the following formula:

=RIGHT(A1, LEN(A1)-SEARCH(" ", A1))

Where:

  • string: A1
  • number_of_characters: LEN(A1) - SEARCH(" ", A1)

In number_of_characters, the SEARCH function returns the position of the first space character in the text. When you subtract this position from the length of the string, you get the length of the substring to extract.

If cell A1 contains “Ashley Holmes”, the formula will return “Holmes”.

Note: For advanced text extraction, use the REGEXEXTRACT function.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Filter Top N per Category in Excel (Using FILTER & COUNTIFS)

You can use a combination of FILTER and COUNTIFS to filter the top N...

Rank per Group in Excel

You have two groups of 20 students each. How do you determine the rank...

Real-Time Excel Filtering Using Combo Box & FILTER Function

In this tutorial, you’ll learn how to set up real-time Excel filtering using a...

Google Sheets: Extract Top N per Group from Query Aggregation

When working with grouped and aggregated data in Google Sheets, you might need to...

More like this

Google Sheets: Extract Top N per Group from Query Aggregation

When working with grouped and aggregated data in Google Sheets, you might need to...

How to Extract Top N from Aggregated Query Results in Google Sheets

To summarize data in Google Sheets, you can use the QUERY function. However, if...

How to Use RANK IF in Google Sheets (Conditional Ranking)

You can use the RANK function to rank values in an entire dataset. But...

5 COMMENTS

  1. Hello,

    Is there a way to use the left, right function with the Query function?
    I’m trying to take YYYYMMDD in the B column, number format into YYYY-MM-DD as date format by query with text function…

    Thank you!

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.