Unlike Google Sheets REGEX text functions, LEFT, RIGHT functions are very straightforward to use. So little to learn the use of it. Further, you can replace both LEFT, RIGHT functions with REGEXEXTRACT. First I’m moving to the syntax part of the LEFT, RIGHT functions and then directly jump to the alternative REGEXEXTRACT formula.
Google Sheets LEFT Function – Syntax
We can use Google Sheets LEFT function to return specific number of characters from the left side of a String.
The below LEFT formula would return “INFO” the first four characters of the string.
Google Sheets RIGHT Function – Syntax
The RIGHT function returns specific number of characters from the right side of a specified string.
The below RIGHT formula would return “INSPIRED” the last eight characters of the string.
See how we can replace or avoid LEFT, RIGHT Functions with Other Functions in Google Sheets
MID Function as an Alternative to LEFT, RIGHT functions in Google Sheets
You can use MID text function as an alternative to Google Sheets LEFT as well as RIGHT functions. Here is the example.
The below MID formula would return the word “INFO” which is equal to the above LEFT formula.
The following MID formula would return the word “INSPIRED” similar to the above RIGHT formula.
Now more interesting part of this tutorial. You can simply use REGEXEXTRACT as an alternative to Google Sheets LEFT as well as RIGHT functions.
See How REGEXEXTRACT Replaces LEFT, RIGHT Functions.
The following REGEXEXTRACT formula is equal to the LEFT formula above. It returns the first four characters from the string “INFO INSPIRED”
Now you can similarly use REGEXEXTRACT to replace RIGHT function. The below formula would return the last 8 characters from the string.
Practical Use of LEFT, RIGHT Functions
I am limiting this part to only one example as an effort to cut short this tutorial.
Let’s check how to find unique texts in a list when first few characters are only matching. Didn’t get? See the screenshot.
The above is few drawing numbers. I want to find the unique drawings based on the first 6 characters.
See the formula. Here I’ve used LEFT function in conjunction with COUNTUNIQUE and it would return the result as # 2.