google sheets value - Free Excel Tutorial

March 17, 2022March 20, 2022

Assigning Points based on Late Time in Google Sheets

It is shown in this lesson how to utilize the IF function in Excel to allocate points based on the amount of time that has passed.

If you intend to pursue along this guide, you may do so by downloading the template spreadsheet.

Generic formula:

=IF(C2<VALUE("0:2"),0,IF(C2<VALUE("0:35"),1,IF(C2<VALUE("0:45"),2,IF(C2<VALUE("1:00"),3,IF(C2<VALUE("3:00"),4,5)))))

Return value

This formula provides the number of points that have been assigned depending on the late time.

How this formula work

For example, below is a list of point regulations and time records; kindly use the formula below to find the appropriate rule or record.

=IF(C2<VALUE("0:2"),0,IF(C2<VALUE("0:35"),1,IF(C2<VALUE("0:45"),2,IF(C2<VALUE("1:00"),3,IF(C2<VALUE("3:00"),4,5)))))

A pop-up with further information will appear if you press the Enter key.

To use this formula, just drag the fill handles over the cells.

IF function is being used to evaluate for defined criteria and then returns the results that match those circumstances. Explain the formula in five parts, as follows:

IF (late_by_time<VALUE ("0:2"),0, It returns 0 if the late time is much less than 2 minutes.

IF (late_by_time <VALUE ("0:35"),1, The function returns 1 if the late time is much less than 35 minutes but higher than or equivalent to 2 minutes.

IF (late_by_time <VALUE ("0:45"),2, If the late time is even less than 45 minutes but higher than or equivalent to 35 mins, it returns 2.

IF (late_by_time <VALUE ("1:00"),3, If the late time is even less than 60 minutes but higher than or equivalent to 45 mins, it returns 3.

IF (late_by_time <VALUE ("3:00"),4, The result is 4 if the late time is less than 3 hours but larger than or equivalent to 60 minutes.

5))))) This function returns five if the late time is more than or equal to three hours.

When converting text to numbers, the value function is employed. It does this by converting the text time to a number.

Related Functions

Google Sheets IF function
The Google Sheets IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Google Sheets VALUE function
The google sheets VALUE function converts a text value that represents a number to a numeric value.The syntax of the VALUE function is as below:= VALUE (text)…

November 5, 2021

Google Sheets VALUE Function

This post will guide you how to use Google Sheets VALUE function with syntax and examples.

Description

The Google Sheets VALUE function converts a text string that represents a number to a numeric value.

The VALUE function can be used to converts a text string in the date, time or number formats into a numeric value in google sheet. The purpose of this function is to convert text to a number and its returned values is a numeric value.

The VALUE function is a build-in function in Google Sheets and it is categorized as a Text Function.

Syntax

The syntax of the VALUE function is as below:

= VALUE (text)

Where the VALUE function argument is:

Text -This is a required argument. The text string that you want to convert it to a number.

Note:

Text can be in any of the constant number, date, or time formats. And if text is not a number, the VALUE function will return #VALUE! Error.
If the VALUE function is not able to convert a text value to a numeric result, it returns a #VALUE! Error message.
VALUE function is provided for compatibility with other spreadsheet applications.

Google Sheets VALUE Function Examples

The below examples will show you how to use google sheets VALUE function to convert string to a number.

#1 To convert a text in B3 cell to a number, just using formula:

= VALUE(B3)

#2 to convert a text string that is not a recognized format (number, date, time format) into a numeric value, using the below formula:

=VALUE(B5)

You will see that the #VALUE! Error will be returned by the VALUE function. Because the text is not a number.

October 29, 2021November 1, 2021

Google Sheets REPLACEB Function

This post will guide you how to use Google Sheets REPLACEB function with syntax and examples.

Description

The Google Sheets REPLACEB function replaces all or part of a text string with another text string based on a number of bytes.

The REPLACEB function can be used to replace characters specified by position in the given text string with another newly text string in google sheets.

The REPLACEB function is a build-in function in Google Sheets and it is categorized as a Text Function.

Syntax

The syntax of the REPLACEB function is as below:

=REPLACEB (old_text, start_num, num_bytes, new_text)

Where the REPLACEB function arguments are:

Old_text -This is a required argument. The text string that you want to replace all or part of
Start_num – This is a required argument. The position of the first character that you want to replace within old_text.
Num_bytes – This is a required argument. The number of characters that you want to replace within old_text
New_text – This is a required argument. The new text that will replace characters in old_text text string.

Note:

The returned results of REPLACEB function are treated as text string in google sheets and if you are using the REPLACEB function with numbers in calculations, the error message may be returned.
The REPLACEB function returns text as the output. And if you wish to return a number, and you can try to use the VALUE function in combination with the REPLACEB function.
The REPLACEB function takes 4 required arguments. The first argument, old_text, is the text string that a part of which will be replaced. The second argument, Start_num is the position where the replacement will begin to replace. The third argument, Num_bytes is the number of bytes in the text to be replaced. The last argument, New_text is the text which will be inserted into the original text string.
If both Start_num and Num_bytes are not a positive number and the REPLACEB function returns a #VALUE error.
It is designed to work with double-type characters set. Such as: simplified Chinese.
REPLACEB function will return the same value as REPLACE function if the old_text has only single byte characters.

Google Sheets REPLACE Function Examples

The below examples will show you how to use google sheets REPLACEB function to replace part of a text string with another new text string.

#1 To replace 4 bytes in B1 cell with a new text string and starting with 7^th character in old_text text, just using formula:

=REPLACEB(B1,7,4," replace") //it returns “google replace1 sheets 2020”

Note: You can use the REPLACEB function to replace a newly text string at a given position in a text string. And you can use the SUBSTITUTE function to replace an existing text with a new text in a string. And Using FIND function and SEARCH function to get the location of a given text string in a text sting.

October 28, 2021November 1, 2021

Google Sheets REGEXREPLACE Function

This post will guide you how to use Google Sheets REGEXREPLACE function with syntax and examples.

Description

The Google Sheets REGEXREPLACE function is to replace a part of a text string with a different substring using regular expressions.

The REGEXREPLACE function can be used to replace a substring from a text string according to a given regular expression in google sheets.

The REGEXREPLACE function is a build-in function in Google Sheets and it is categorized as a Text Function. And The REGEXREPLACE function is one of the three REGEX functions (REGEXEXTRACT, REGEXMATCH and REGEXREPLACE )in google sheets.

Syntax

The syntax of the REGEXREPLACE function is as below:

=REGEXREPLACE (text, regular_expression, replacement)

Where the REGEXREPLACE function arguments are:

text -This is a required argument. The text string that you want to extract a substring based on regular expression.
regular_expression – This is a required argument. The Regular_expression is a regular expression. It can be used to match the part of the text that you want to extract. The regular expression argument should be entered in double-quote characters. And Regular_expression is a search patterns that allow you to find a certain patterns in original text string.
Replacement – The text which will be inserted into the original text.

Note:

The REGEXREPLACE function takes two arguments. It checks whether a piece of text matches a given regular expression.
The REGEXREPLACE function only works with text value as its first argument and returns a text result. If you wish to return a numeric value, and you need to use VALUE function in combination with the REGEXREPLACE function. If the input value is a number, and you can use the TEXT function to convert them.
Google Sheets supports RE2 for regular expressions.

Google Sheets REGEXREPLACE Function Examples

The below examples will show you how to use google sheets REGEXREPLACE function to replace a substring from a text according to a regular expression.

#1 Replace all “google” keywords with “mygoogle” text string, and you can use the REGEXREPLACE function with a regular expression “google”, type:

=REGEXREPLACE(B1,"google","mygoogle") //it returns “mygoogle 2021 sheets 2020”

#2 Replace all word with “mygoogle” substring from a string in cell B1, and you can use the REGEXREPLACE function with a regular expression “\w+”, type:

=REGEXREPLACE(B1,"\w+","mygoogle") //it returns “mygoogle mygoogle mygoogle mygoogle”

#3 Replace the last whole word from a string using REGEXREPLACE function with a regular expression “\w+$”, type:

=REGEXREPLACE(B1,"\w+$","mygoogle") // it returns “google 2021 sheets mygoogle”

#4 Replace all numbers with “2025” from a string in cell B1 and you can use the REGEXREPLACE function with a regular expression “\d+”, type:

=REGEXREPLACE(B1,"\d+","2025") // it returns “google 2025 sheets 2025”

#5 Replate the last number with “2025” number from a string in cell B1, and you can use the REGEXREPLACE function with a regular expression “\d+$”, type:

=REGEXREPLACE(B1,"\d+$","2025") // it returns “google 2021 sheets 2025”

October 27, 2021November 1, 2021

Google Sheets REGEXMATCH Function

This post will guide you how to use Google Sheets REGEXMATCH function with syntax and examples.

Description

The Google Sheets REGEXMATCH function is to check whether a piece of text matches a regular expression.

The REGEXMATCH function can be used to check whether a substring from a text string matches a given regular expression in google sheets. And it returns a logic value, TRUE or FALSE.

The REGEXMATCH function is a build-in function in Google Sheets and it is categorized as a Text Function.

Syntax

The syntax of the REGEXMATCH function is as below:

= REGEXMATCH (text, regular_expression)

Where the REGEXMATCH function arguments are:

text -This is a required argument. The text string that you want to extract a substring based on regular expression.
regular_expression – This is a required argument. The Regular_expression is a regular expression. It can be used to match the part of the text that you want to extract. The regular expression argument should be entered in double-quote characters.

Note:

The REGEXMATCH function takes two arguments. It checks whether a piece of text matches a given regular expression.
The REGEXMATCH function only works with text value as its first argument and returns a text result. If you wish to return a numeric value, and you need to use VALUE function in combination with the REGEXEXTRACT function. If the input value is a number, and you can use the TEXT function to convert them.
Google Sheets supports RE2 for regular expressions.

Google Sheets REGEXMATCH Function Examples

The below examples will show you how to use google sheets REGEXMATCH function to extract a substring from a text according to a regular expression.

#1 Check if text value in Cell B1 matches a given regular expression, using the REGEXMATCH function with a regular expression “google” , type:

= REGEXMATCH (B1,"google") //it returns “TRUE”

#2 Check if text value in cell B1 matches either of the words in a sentence using REGEXMATCH function with a regular expression “google|sheets” , type:

=REGEXMATCH(B1,"google|sheets") // it returns “TRUE”

#3 Check if text value in Cell B1 matches any number of decimal digits using REGEXMATCH function with a regular expression “\d+” , type:

= REGEXMATCH(B1,"\d+") // it returns “TRUE”

#4 Check if text value in Cell B1 matches any specific decimal digits using REGEXMATCH function with a regular expression “[2-3]” , type:

= REGEXMATCH(B1,"[2-3]") // it returns “TRUE”

October 27, 2021November 1, 2021

Google Sheets REGEXEXTRACT Function

This post will guide you how to use Google Sheets REGEXEXTRACT function with syntax and examples.

Description

The Google Sheets REGEXEXTRACT function uses a regular expression to extract the first matching substring.

The REGEXEXTRACT function can be used to extract the first matching substring from a text string according to a given regular expression in google sheets. So it returns the part of the specified string that matches the given pattern in a regular expression.

The REGEXEXTRACT function is a build-in function in Google Sheets and it is categorized as a Text Function.

Syntax

The syntax of the REGEXEXTRACT function is as below:

=REGEXEXTRACT (text, regular_expression)

Where the REGEXEXTRACT function arguments are:

text -This is a required argument. The text string that you want to extract a substring based on regular expression.
regular_expression – This is a required argument. The Regular_expression is a regular expression. It can be used to match the part of the text that you want to extract. The regular expression argument should be entered in double-quote characters.

Note:

The REGEXEXTRACT function takes two arguments. It extracts the first matching substrings according to a given regular expression.
The REGEXEXTRACT function also can return multiple results with capture groups. A capture group is also a part of a regular expression that can be enclosed in parentheses.
The REGEXEXTRACT function only works with text value as its first argument and returns a text result. If you wish to return a numeric value, and you need to use VALUE function in combination with the REGEXEXTRACT function. If the input value is a number, and you can use the TEXT function to convert them.
Google Sheets supports RE2 for regular expressions.

Google Sheets REGEXEXTRACT Function Examples

The below examples will show you how to use google sheets REGEXEXTRACT function to extract a substring from a text according to a regular expression.

#1 Extract the first 5 characters from a string using REGEXEXTRACT function with a regular expression “…..” , type:

=REGEXEXTRACT(B1,".....") //it returns “googl”

#2 Extract the last 5 characters from a string using REGEXEXTRACT function with a regular expression “…..$” , type:

=REGEXEXTRACT(B1,".....$") // it returns “heets”

#3 Extract the first whole word from a string using REGEXEXTRACT function with a regular expression “\w+” , type:

=REGEXEXTRACT(B1,"\w+") // it returns “google”

#4 Extract the last whole word from a string using REGEXEXTRACT function with a regular expression “\w+$” , type:

=REGEXEXTRACT(B1,"\w+$") // it returns “sheets”

#5 Extract the first number from a string using REGEXEXTRACT function with a regular expression “\d+” , type:

=REGEXEXTRACT(B1,"\d+") // it returns “2021”

#6 Extract the last number from a string using REGEXEXTRACT function with a regular expression “\d+$” , type:

=REGEXEXTRACT(B1,"\d+") // it returns “2020”