In Google Sheets, there are a variety of ways to extract numbers, text, and punctuation from a cell.
Learning all of the many procedures that may be used to extract a substring from a string can be intimidating at first, but if you are a regular spreadsheet user, it is well worth your time to learn these functions.
In this article we’ll see how to use Google Sheets to extract numbers or text from a string.
There are many functions for extracting in Google Sheets and we can use them according to our needs. So let’s see them all and their steps to use.
The LEFT function returns a substring from the beginning of a string, based on the amount of characters supplied.
The LEFT function has the following syntax:
string – the string from which you want to return the substring
number_of_characters – the number of characters to be returned from the left side of the string This is an optional argument that will be set to 1 by default.
As you can see, the function simply returns a substring that starts on the left of your string and is the length of the second parameter of the function (number of characters).
The RIGHT function is the polar opposite of LEFT. It will extract a substring from the end of a string based on the amount of characters given.
The RIGHT function has the following syntax:
string – the string from which you want to return the substring
number_of_characters – the number of characters to return from the right side of the string This is an optional argument that will be set to 1 by default.
As you can see from the example above, this function will simply extract the number of characters supplied in the second argument (number of characters) starting on the right-hand side of your string.
The MID function extracts a substring from the centre of a string and returns it. This method allows you to specify where you want to start your extraction and how many characters you want to extract.
The MID function has the following syntax:
=MID(string, starting_at, extract_length)
string – the string from which you want to extract the substring
starting_at – this is the index of the character that you want to start your extraction from.It begins on the left, with the first character having an index of 1
extract_length – the length of the string to be extracted. You’ll enter the number of characters you’d like to extract here.
Here is a n example of this function below
Examine how the function extracts the substring starting at the character specified in the second (starting at) parameter and the length specified in the third (extract length) parameter in the example above.
So, in my sheet, the first formula =MID(A2,1,2) will take the string in A2, start at the first character, and return two characters.
The SPLIT function takes a string in a cell and divides it according to the character or string supplied, resulting in portions of the string being placed in other cells.
The SPLIT function has the following syntax:
=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
text – the text that you want to split into many cells
delimiter – this is the character or characters that the text will be split up by.
split_by_each – this parameter specifies whether you separate text around each character used as the delimiter or divide text using the full delimiter. If this parameter is set to TRUE and your delimiter is “abc,” your text will be split around each character of “a,” “b,” and “c.” Your text will be split around “abc” if it is set to FALSE. This argument is optional and defaults to TRUE.
remove_empty_text – This argument determines whether or not empty text should be removed from split cells. It is optional and defaults to TRUE. If FALSE is selected, empty cells will be placed between delimiters.
Look at how the character used as my delimiter argument in the formula splits my cell in the example above. Pay attention to how altering the split by each option from TRUE to FALSE affects how the cells are split in the 4th and 5th rows of the example. This should give you a better idea of how this function works.
The REGEXEXTRACT function is useful for extracting data from a substring. This function will extract a substring of data that matches the regular expression you specify.
REGEXEXTRACT has the following syntax:
=REGEXEXTRACT(text, regular_expression) here,
text – the string from which the regular expression will be extracted
regular_expression – the text that will be returned in its entirety.
Using capture groups, you can return several results with this function. These are elements of parenthesis-enclosed patterns. The function will return the whole match if you don’t use capture groups.
RE2 is the regular expression engine used by Google Sheets. The complete list of acceptable values can be seen here. Check out this resource on regular expression syntax for a fast reference on the most popular characters.
It’s worth noting that if you use regular expressions with this function and then add a plus sign (+) after your regular expression, your extracted value will display several characters. Only one character will be returned if plus sign wouldn’t be used.
The REGEXREPLACE function replaces a portion of a text string that matches a regular expression with a replacement text.
Syntax for this function is –
=REGEXREPLACE(text, regular_expression, replacement)
text – the text that will be used, with a portion of it being substituted
regular_expression – he part of the text that will be substituted is known as the expression.
replacement – the text that will be used in place of the original.
REGEXREPLACE uses the same syntax for regular expressions as REGEXTRACT.
Here is an example to this function.
Extraction in Google Sheets Cheat Sheet
Now that we have covered the basics of the functions used for extractions, here are some formulas that you can copy and paste into your own sheets for some common extraction tasks:
Extract the Last Name
=RIGHT(A2,LEN(A2)-FIND(“*”,SUBSTITUTE(A2,” “,”*”,LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)))))
Extract First Word/Name
Extract Text from String
Extract Numbers from String
Here is an example of each of these formulas in a spreadsheet:
In any spreadsheet programe, knowing how to parse strings and extract the appropriate characters is a valuable skill to master. Being able to put together a formula to get the data you need would greatly speed up productivity when working with files containing thousands of lines.