16. Text and Date Functions in Google Sheets
The text and date functions are essential for any user who wants to master Google Sheets. They allow you to manipulate and format strings (texts) and dates efficiently, facilitating the organization, analysis and presentation of data. In this chapter, we'll explore some of the most useful functions and how to apply them to your spreadsheets.
Text Functions
Google Sheets offers a variety of text functions that can be used to change the appearance of data, combine information from multiple cells, or extract specific parts of a string. Let's see some of them:
- UPPER: This function converts all text in a cell to uppercase letters. For example,
=UPPER("google")will result in "GOOGLE". - LOWER: The opposite of UPPER, this function transforms the text to lowercase.
=LOWER("GOOGLE")will result in "google". - PROPER: This function makes the first letter of each word in a string capitalized.
=PROPER("google sheets")will result in "Google Sheets". - TRIM: Removes extra spaces at the beginning and end of a text, as well as duplicate spaces between words.
=TRIM(" google sheets ")will result in "google sheets". - CONCATENATE or &: Both combine two or more strings into one.
=CONCATENATE("Google", " ", "Sheets")or="Google" & " " & "Sheets"will result in "Google Sheets". - LEFT, RIGHT, MID: These functions extract subsets of a string. LEFT returns the first n characters, RIGHT the last n characters, and MID returns a sequence of characters from any part of the string. For example,
=LEFT("Google", 3)will result in "Goo",=RIGHT("Sheets", 2)in "ts", and=MID("Sheets", 2, 3)in "hee". - LEN: Returns the length of a string, that is, the number of characters it contains.
=LEN("Google")will result in 6. - FIND and SEARCH: These functions are used to find the position of one string within another. The difference is that SEARCH is case insensitive, while FIND is not.
=FIND("G", "Google")will result in 1, and=SEARCH("g", "Google")will also result in 1. - REPLACE and SUBSTITUTE: REPLACE replaces part of a string with another, based on the position of the characters, while SUBSTITUTE replaces all occurrences of a string with another.
=REPLACE("Google Sheets", 1, 6, "Docs")will result in "Docs Sheets", and=SUBSTITUTE("Google Sheets Sheets", "Sheets", "Docs" )will result in "Google Docs Docs".
Date Functions
The date functions in Google Sheets are essential for working with schedules, deadlines and temporal analysis. Some of the most important functions include:
- NOW: Returns the current date and time.
=NOW()can display, for example, "03/03/2023 14:45:00". - TODAY: Returns the current date without the time.
=TODAY()can display "03/03/2023". - DATE: Creates a date from the given year, month and day values.
=DATE(2023, 3, 3)will result in "03/03/2023". - EDATE: Calculates the date that is a specified number of months before or after a specified date.
=EDATE("03/03/2023", 1)will result in "03/04/2023" (one month after the start date). - DATEVALUE: Converts a string representing a date into a date value that Google Sheets can understand.
=DATEVALUE("03/03/2023")will result in a serial date that Google Sheets uses internally for date calculations. - DAY, MONTH, YEAR: These functions extract the day, month and year from a date, respectively.
=DAY(TODAY())will return the current day,=MONTH(TODAY())the current month, and=YEAR(TODAY()) the current year. - WEEKNUM: Returns the number of the week in the year for a given date.
=WEEKNUM(TODAY())can display, for example, 10, if today is in the tenth week of the year. - WORKDAY and NETWORKDAYS: WORKDAY calculates the date that is a given number of business days ahead or behind a start date, while NETWORKDAYS calculates the number of business days between two dates.
=WORKDAY(TODAY(), 5)will result in the date five business days after today, and=NETWORKDAYS(TODAY(), DATE(2023,3,10))will calculate business days between now and 03/10/2023.
Practical Examples
Let's now see how some of these functions can be combined to solve common spreadsheet problems:
- Suppose you have a list of names in a column and you want to ensure that they all have the first letter capitalized, regardless of how they were entered. You can use the PROPER function for this:
=PROPER(A2). - If you need to extract the year from a date in a cell for temporal analysis, you can use the YEAR function:
=YEAR(B2). - To combine first and last names that are in separate columns into a single column, you can use the concatenation operator:
=A2 & " " & B2. - If you are planning an event and need to calculate the date that is 30 working days before the event, you can use the WORKDAY function:
=WORKDAY(C2, -30), assuming that the date of the event is in cell C2.
Once you have mastered these text and date functions, you can perform a wide range of tasks in Google Sheets, from formatting and manipulating strings to calculating deadlines and analyzing trends over time.
It's important to practice and try out these functions in your own spreadsheets to fully understand their potential. Remember that often the solution to a problem will involve combining several functions, so don't hesitate to be creative and explore the possibilities that Google Sheets offers!
