Last Updated on January 18, 2024 by Jake Sheridan
TEXT Function in Google Sheets
The Google Sheets TEXT function converts a number to text according to a specified format like dates and times, decimal places, currency, duration and more.
=TEXT (number, format)
- number – A numeric value
- format – The number format for the output
When using the TEXT function in Google Sheets, the function creates a text string, not a number.
If you want to change the format of a number without changing it to a text string, use number formatting.
One more thing to keep in mind: the TEXT function will override any formatting applied to the cell.
Google Sheets TEXT Examples
Here are some text formula examples following this syntax:
=TEXT(1.23,"$0.00")– Converts the number 1.23 into a text string formatted as “$1.23”.
=TEXT(A2,"#.###")– Converts the value in cell A2 into a text string with up to three decimal places (without trailing zeros).
=TEXT(24,"#.0?")– Converts the number 24 into a text string with one decimal place, showing a “?” if there isn’t a value for the second decimal place.
=TEXT(DATE(1969,7,20),"MM-yyyy")– Converts the date corresponding to July 20, 1969, into a text string formatted as “07-1969”.
40+ Google Sheets Text How-Tos
Looking for more ways to manipulate text in Google Sheets?
Below you will find 40+ step-by-step instructions for doing (almost) everything you can imagine* with text in Google Sheets.
(*okay, maybe not every single thing you can imagine, but a lot of stuff!)
Each tutorial is grouped under these categories to make it a little easier to navigate:
- Formatting Text and Cells
- Changing Text Attributes
- Adding and Managing Elements
- Working with Text Functions
- Data Sorting
Formatting Text and Cells in Google Sheets
Master the aesthetic of your Google Sheets by learning how to customize and adjust the appearance of your text and cells.
How to add bullet points
- Bullets can help break down information into bite-sized pieces and make documents appear more professional and easier to understand.
- There are keyboard shortcuts to insert one of three basic bullet characters.
- Bullets can also be copied and pasted from the internet.
More detailed tutorial: How To Add Bullet Points In Google Sheets
How to center text
- Text in Google Sheets is left-aligned by default.
- To center text, select the cell or range and click on the Horizontal Align icon in the toolbar.
- All values in the selected cells will be centered, including numbers, dates and any other data format.
More detailed tutorial: How To Center Text In Google Sheets
How to make text fit in cell
- Wrap text in a cell in Google Sheets by selecting the cell and clicking on the Wrap text option.
- Adjust the column width and row height to make the text more readable.
- Merge multiple cells together to present longer text without having to modify the column widths.
More detailed tutorial: How to Fit Text to Cell in Google Sheets
How to make text vertical
- Vertical text can be useful if you need to save space in your spreadsheet or you might simply want to display text vertically for aesthetic purposes.
- Google Sheets offers a lot of formatting tools to help you fully customize your spreadsheets.
- Users can also choose to stack letters vertically, with each letter in the text occupying a new line rather than being read as a single word.
More detailed tutorial: How To Make Text Vertical In Google Sheets
How to wrap text
- Text wrapping is when the text fits inside the frame or object (or in this case, Google Sheets cell).
- There are three different options for text wrapping: Overflow, Wrap, and Clip.
- You can wrap text in an entire spreadsheet or individual cells.
More detailed tutorial: How to Wrap Text in Google Sheets
How to fit text to cell
Text wrapping is a useful method for making text fit in cells.
Cell resizing can also be used to make text fit in cells.
Cell merging can be used when text wrapping and resizing don’t give the desired effect.
More detailed tutorial: How To Make Text Fit In Cell In Google Sheets
How to highlight text
- Cells can be manually highlighted by changing the text or fill color.
- Conditional formatting can be used to automatically highlight cells that meet certain criteria.
- Cells that follow the conditional formatting rules should be highlighted automatically.
More detailed tutorial: How to Highlight in Google Sheets
How to indent
- Indenting text in Google Sheets can help visually break up the spreadsheet.
- To create a custom number format that indents cells, type four spaces followed by an at sign (@) into the Custom Number Format field.
- To remove indenting, select the Automatic option from the Numbers submenu in the Format menu.
More detailed tutorial: How To Indent On Google Sheets
Google Sheets indent text
- Custom number formatting can be used to indent text in specific cells.
- An ARRAYFORMULA can be used to append whitespace and the cell contents to create indentations.
- A copy of the example spreadsheet can be made to test out the indentation.
More detailed tutorial: How To Indent Text In Google Sheets
Changing Text Attributes in Google Sheets
Discover how to change and modify text attributes to enhance your data presentation in Google Sheets.
How to change default font
- Google Sheets comes with several premade themes that include a set of colors and a default font.
- You can customize a theme to change the default font.
- The new font will be applied to all existing and new tabs in the spreadsheet.
More detailed tutorial: How To Change Default Font In Google Sheets
How to change case
- The UPPER(), LOWER(), and PROPER() functions can be used to change the case of text in Google Sheets.
- The UPPER() function changes text to all uppercase.
- The LOWER() function changes text to all lowercase.
- The PROPER() function changes text to proper case.
More detailed tutorial: How To Change Case In Google Sheets
How to capitalize
- The UPPER() function can be used to capitalize text in Google Sheets.
- The UPPER() function works for all standard letters in the Roman Alphabet, as well as characters with diacritics.
- The formula for capitalizing text is “=UPPER(A2)”, where A2 is the reference for the cell you want to capitalize.
More detailed tutorial: How To Capitalize In Google Sheets
How to change text to all caps
- The UPPER function can be used to convert text to all caps in Google Sheets.
- The AutoFill tool can be used to quickly convert multiple text values to all caps.
- Text can also be converted to all caps in Google Docs.
More detailed tutorial: How To Change Text To All Caps In Google Sheets
Google sheets spell check
- Spell check can be used to check for spelling errors in a Google Sheets document.
- The spell check feature can be used to check the entire sheet or just the selected cells.
- Users can add flagged words to their personal dictionary.
More detailed tutorial: How to Use Spell Check in Google Sheets
How to spell check
- You can spell check a single selection, an entire sheet, or all sheets in a Google Sheet.
- You can add words to the personal dictionary to avoid having to click Ignore for each of these words.
- You can directly add new words to your dictionary (or delete existing ones) by opening the Tools menu and choosing Personal Dictionary from the Spelling submenu.
More detailed tutorial: How To Spell Check Google Sheets
Adding and Managing Elements in Google Sheets
From adding borders to inserting special characters, dive into how to enrich your Google Sheets with additional elements and features
How to add borders
- Borders can be added to individual cells or to a range of any size.
- The borders menu provides different border options to choose from.
- The color and style of the borders can be changed.
More detailed tutorial: How To Add Borders In Google Sheets
How to add a line
- The simplest way to add a new line is to use the keyboard shortcut ALT+ENTER.
- To add a line to a cell that already has text in it, select the cell and hit enter to enter edit mode, then click on the location in the text that you want to add a line to put the cursor there and hit ALT+ENTER to insert the new line.
- To add new lines to a formula, use the CHAR(10) function and join any string segments together with the “&” character.
More detailed tutorial: How To Add A Line In Google Sheets
How to add a new line in cell
- You can add a new line in a cell in Google Sheets using the Alt + Enter keyboard shortcut.
- You can also use the Wrap Text option to automatically add a new line in a cell if the text extends the current width of the cell.
- You can make a copy of the example spreadsheet to test out the steps.
More detailed tutorial: How To Add a New Line In Cell in Google Sheets
How to add a tab
- Click on the plus icon in the lower-left corner of the screen to add a new tab.
- Right click on the new tab and choose Rename to give it a descriptive name.
- Repeat these steps as many times as necessary to add multiple tabs to your spreadsheet.
More detailed tutorial: How To Add A Tab In Google Sheets
How to insert text box
- Open the Insert menu and click on Drawing to open the Drawing Editor window.
- Select a shape from the Shapes menu and click and drag on the canvas to create the shape.
- Use the Fill and Border Icons in the toolbar to change the color and border style of the textbox.
More detailed tutorial: How To Insert Text Box In Google Sheets
How to insert Google Sheets into Google Docs
- Select the desired range of cells in the Google Sheet and copy it.
- Paste the copied content in the desired location in the Google Doc and select the Link to Spreadsheet option.
- To update the table, click the UPDATE button in the upper right corner of the table.
More detailed tutorial: How To Insert Google Sheets Into Google Docs
How to insert Google Sheets into Google Slides
- Copy the data from the Google Sheets file and paste it into the Google Slides file.
- Select the option to link the data so that changes in the spreadsheet will carry over to the Slides file.
- Click the Update button to update the Google Sheets data.
More detailed tutorial: How To Insert Google Sheets Into Google Slides
How to insert special characters
- Google Sheets does not have a feature to insert special characters, but there are workarounds.
- One workaround is to copy special characters from Google Docs.
- Another workaround is to copy special characters from the internet.
More detailed tutorial: How To Insert Special Characters In Google Sheets
How to insert symbols
- Symbols can be inserted into Google Sheets by copying and pasting them from the internet.
- Symbols can also be inserted into Google Sheets by using the Insert Special Characters window in Google Docs.
- Symbols can be pasted into existing text in a cell by double clicking on the cell to enter edit mode.
More detailed tutorial: How To Insert Symbols In Google Sheets
How to insert page break
- Open the File menu and choose Print to access the Print Settings screen.
- Drag the dotted lines from the edges of the document to add page breaks.
- Manually set page breaks will appear as solid blue lines instead of dotted.
More detailed tutorial: How To Insert Page Break In Google Sheets
How to use slicer
- Slicers are a powerful tool for filtering data in Google Sheets.
- Adding a slicer requires selecting the data, opening the Data menu, and choosing Add a Slicer.
- To filter with the slicer, select a column, toggle the visibility of the values, and click OK.
More detailed tutorial: How To Use Slicer In Google Sheets
How to view page breaks
- Open the File menu and select Print to view and edit custom page breaks.
- Dotted blue lines will appear on the preview showing where the page breaks currently are.
- Click Confirm Breaks when you’re done editing, then Next on the next screen and the custom page breaks will be saved.
More detailed tutorial: How To View Page Breaks In Google Sheets
How to go down a line
- Select the cell you want to add a line to and use the keyboard shortcut Alt+Enter to add a new line.
- Use the CHAR function to add a line break to a text string.
- Use the ‘&’ symbol to append the values in a cell with a line break in between.
More detailed tutorial: How To Go Down A Line In Google Sheets
How to line break
- The fastest way to add a line break is using the keyboard shortcut CTRL+ENTER.
- Line breaks can also be added to cells that already have text in them.
- Formulas can be used to automatically generate text with line breaks.
More detailed tutorial: How To Line Break In Google Sheets
How to add multiple lines in one cell
- The JOIN function can be used to add multiple lines of text into one cell.
- The CHAR function can be used to specify a newline character as a delimiter for a range of values.
- Alt+Enter can be used to manually add multiple lines in one cell.
More detailed tutorial: How To Add Multiple Lines In One Cell in Google Sheets
How to wrap text in cell
- Text wrapping is a formatting option that allows users to fit lengthy strings of text within the confines of a cell.
- To apply text wrapping, select the cell or cell range and click on the Text Wrapping icon in the toolbar.
- Users can also drag the edge of the cell to the right to expand the width if the text wrapping format makes the cell too narrow.
More detailed tutorial: How To Wrap Text In Cell in Google Sheets
How to superscript
- Superscript characters are set slightly above the rest of the text and are usually smaller than surrounding characters.
- Google Sheets doesn’t support formatting characters as superscript, but Unicode superscript characters can be used.
- Superscript characters can be added to a spreadsheet by copy/pasting them.
More detailed tutorial: How To Superscript In Google Sheets
How to make a degree symbol
- The simplest method to insert a degree symbol is to copy and paste it into the spreadsheet.
- The CHAR() function can also be used to insert the degree symbol by using its unicode number.
- An example spreadsheet is provided to help with the process.
More detailed tutorial: How To Make A Degree Symbol On Google Sheets
Working with Text Functions in Google Sheets
Explore the power of Google Sheets’ text functions, allowing you to manipulate, combine, and analyze your data in numerous ways.
Counting cells with text
- The COUNTA function requires at least one argument, such as a list of cells to check or a particular cell range to scan through.
- The SUMPRODUCT function can be used to count all cells with text in a range by using the TRIM and LEN functions.
- The Summary section in the lower-right-hand side of the sheet can also be used to find the number of non-empty cells.
More detailed tutorial: How To Count Cells with Text in Google Sheets
How to insert text box
- Google Sheets has a Drawing feature that allows users to insert various shapes and visual elements to a spreadsheet.
- Users can create a rectangle object using the Drawing tool and add text inside to create a text box.
- The Drawing tool also allows users to modify the formatting of the shape itself such as the shape’s outline and background color.
More detailed tutorial: How to Insert a Text Box in Google Sheets
Checking if cell contains text
- The SEARCH, REGEXMATCH, and FIND functions can be used to check if a cell contains a substring.
- The IFERROR function can be used to catch cases where no substring is found.
- The REGEXMATCH function can be used to ignore case when finding matches.
More detailed tutorial: Checking if a Cell Contains a Substring in Google Sheets
How to use the text function
- The TEXT function can be used to convert a number to a specific format, format dates and times, or even round the number to a specific significant digit.
- The ‘0’ digit can be used to force Google Sheets to display zeros if a number has fewer digits than the specified format.
- The TEXT function can also be used to convert decimals into percentages and format time values.
More detailed tutorial: How to Use the TEXT Function in Google Sheets
How to combine text from two cells
- The “&” character can be used to append text together.
- The CONCAT, CONCATENATE, and JOIN functions can be used to convert multiple string values into a single string.
- The JOIN function can be used to output a string composed of all strings in a specified range.
More detailed tutorial: How to Combine Text From Two Cells in Google Sheets
How to convert text to number
- Text data can be converted to numerical values using the number formatting options.
- The VALUE function can be used to convert text values into numbers.
- Dates written as text can be converted into date values as long as the date formats are valid and recognized by the program.
More detailed tutorial: How to Convert Text to Number in Google Sheets
Sum if cell contains text
- The SUMIF function in Google Sheets is a powerful tool for adding up cells based on certain criteria.
- You can use the SUMIF function to sum cells only if a corresponding cell in the same row contains text values.
- You can use the string “<>” to indicate you want to check if your values are non-blank.
More detailed tutorial: How to SUMIF Cells If Contains Text in Google Sheets
How to add text to formula
- The “&” character can be used to combine text strings with formula results.
- Strings can be used to add meaningful output to a cell.
- The TEXT function can be used to format the current date into a specific format.
More detailed tutorial: How To Add Text To Formula in Google Sheets
How to add a bullet list
- The CHAR function can be used to insert a bullet point character or any other character.
- Custom number formatting can be used to add a bullet point to the start of each value.
- Keyboard shortcuts can be used to insert a bullet point manually for each value in a list.
More detailed tutorial: How To Insert a Bullet List In Google Sheets
How to add a numbered list
- Google Sheets does not have a built-in function to create a numbered list.
- The CONCATENATE formula can be used to append a number before every value in a range.
- Users can customize the format of the numbered list.
More detailed tutorial: How to Make a Numbered List in Google Sheets
How to Insert Bullet Points
- The CHAR function can be used to insert bullet points into cells in Google Sheets.
- The ‘&’ concatenate symbol can be used to add text from another cell after the bullet point.
- The Fill Handle tool can be used to convert an entire range of strings into a bullet list.
More detailed tutorial: How to Insert Bullet Points in Google Sheets
Substitute Google Sheets
- he SUBSTITUTE function in Google Sheets allows you to replace text in a cell with a different text string.
- You can specify the occurrence to change a specific instance of a string rather than every occurrence in the target text.
- You can make a copy of the example spreadsheet to test out the SUBSTITUTE function.
More detailed tutorial: How to Use the SUBSTITUTE Function in Google Sheets
Data Sorting in Google Sheets
Improve your data management skills in Google Sheets with tips on efficient sorting, organizing, and presenting of your data.
How to alphabetize
- Select the field you want to sort your sheet by.
- Right-click on the column header and select either ‘Sort sheet A to Z’ or Sort sheet Z to A’.
- Use the Freeze feature to keep the first column in place.
More detailed tutorial: How To Alphabetize In Google Sheets
Hopefully this guide has given you an overview of working with text in Google Sheets.
Explore some of the other useful resources on Sheets for Marketers:
- Google Sheets Templates – A collection of over 200 Google Sheets templates for SEO, marketing, PPC and more.
- Google Sheets Tutorials – A growing collection of spreadsheet tutorials for doing (almost) everything in Google Sheets.
- Data Studio Templates – A collection of over 50 Google Data Studio templates for SEO reporting.