Text functions can help reduce repetitive and error-prone work by editing textual data in bulk.
Text functions are used when working with textual data in a workbook and can perform operations like merging multiple strings of text (CONCAT and CONCATENATE), splitting one string of text into multiple strings (SPLIT), cleaning text (PROPER), and more.
Textual data often plays a role in even the most numbers-centric spreadsheets like financial calculators and balance sheets, and using text functions can help reduce the amount of repetitive work that is sometimes needed to edit and manage text.
How to Use Text Functions
In the examples below, we’ll take a look at some commonly used text functions. For documentation on every text function supported by Spreadsheet.com, reference our section on Text functions.
When using functions in your workbook, you can input and edit formulas either directly in the cell, or from the formula bar between the worksheet toolbar and the spreadsheet grid. Learn more in our article on Creating and Editing Formulas.
How to Merge Multiple Strings of Text (CONCAT and CONCATENATE)
Sometimes it’s necessary to combine text from multiple cells into one value. In our Volunteer Sign Up template, for example, we use the CONCATENATE function to combine multiple columns of event details into an event’s unique identifier.
In the example above, we’ve used the CONCATENATE function in Cells C2 through C4 to combine three strings of text – a last name (Column B), a comma (defined explicitly), and a first name (Column A) – into one "Full Name" value.
CONCAT performs a similar function to CONCATENATE, but can only combine two strings of text. Note that when using CONCAT, you cannot include a space or other delimiter between the two combined strings.
How to Fix Messy Text (PROPER, UPPER, and LOWER)
The text function PROPER, which capitalizes the first letter of all words in a text string, can be useful in cleaning up messy data and fixing data entry errors.
In the example above, the names in Column A have been entered erroneously. Instead of going through them one by one to correct them, we can instead use the PROPER function in Cells B2 through B4. The PROPER function returns the adjacent string with correct capitalization.
Similarly, the UPPER function will automatically convert every letter of a text string to lowercase, and the LOWER function will automatically convert every letter of a text string to uppercase.
How to Display Formulas (FORMULATEXT)
The FORMULATEXT function displays the formula expression input into another cell and can be useful to reveal the calculations behind your workbook when sharing it with someone else.
In the example above, we’ve used the FORMULATEXT function in Cell C5 to display the formula being used to calculate the total in B5. In fact, all of the examples in this article use the FORMULATEXT function in conjunction with the CONCATENATE function.
Above, Cell E5 displays the formula used in Cell D5, which in turn displays the formula used in Cell C5. The rest of the green example formulas shown in this article (and the rest of our Functions articles) follow a similar format.
More Text Functions
The functions shown above are just some of the many text functions supported by Spreadsheet.com. Take a look at our full suite of articles on text functions to learn more.
Learn about enhancing your workbooks’ textual data with Spreadsheet.com’s unique Data Types. Or, read on to learn more about other types of functions supported by Spreadsheet.com, including lookup functions, logical functions, statistical functions, and more.