Module: M1-R5: Information Technology Tools and Network Basics
Chapter: Spreadsheet
Text functions in LibreOffice Calc and Excel help manipulate and analyze textual data — extracting characters, joining text, converting case, and more. These functions are essential for cleaning and preparing data for reporting and automation.
Purpose: Extracts a specified number of characters from the left side of a text string.
Formula: =LEFT("LIBREOFFICE";5)
Result: LIBRE
Purpose: Extracts a specified number of characters from the right side of a text string.
Formula: =RIGHT("SPREADSHEET";5)
Result: SHEET
Purpose: Extracts characters from the middle of a text string, starting at a specified position.
Formula: =MID("CALCULATOR";3;4)
Result: LCUL
Purpose: Returns the total number of characters in a text string.
Formula: =LEN("LibreOffice")
Result: 11
Purpose: Returns the position of a substring within a text string (case-sensitive).
Formula: =FIND("O";"LibreOffice")
Result: 6 (because "O" appears at the 6th position)
Purpose: Joins two or more text strings into one.
Formula: =CONCATENATE("Hello ","World!")
Result: Hello World!
Purpose: Joins multiple text items using a delimiter (available in newer Calc versions).
Formula: =TEXTJOIN(", ";1;A1:A3)
Example Data:
| Cells | Data |
|---|---|
| A1 | Apple |
| A2 | Banana |
| A3 | Cherry |
Result: Apple, Banana, Cherry
Purpose: Converts text to proper case (first letter capitalized).
Formula: =PROPER("welcome to quitexams")
Result: Welcome To Quitexams
Purpose: Converts all text to lowercase.
Formula: =LOWER("HELLO WORLD")
Result: hello world
Purpose: Converts all text to uppercase.
Formula: =UPPER("hello world")
Result: HELLO WORLD
Purpose: Repeats text a specified number of times.
Formula: =REPT("*";10)
Result: **********
| Function | Description | Example | Result |
|---|---|---|---|
| LEFT | Extracts leftmost characters | =LEFT("LIBREOFFICE";5) | LIBRE |
| RIGHT | Extracts rightmost characters | =RIGHT("SPREADSHEET";5) | SHEET |
| MID | Extracts middle characters | =MID("CALCULATOR";3;4) | LCUL |
| LEN | Counts characters | =LEN("LibreOffice") | 11 |
| FIND | Finds character position | =FIND("O";"LibreOffice") | 6 |
| CONCATENATE | Joins text | =CONCATENATE("Hello ","World!") | Hello World! |
| TEXTJOIN | Joins text with delimiter | =TEXTJOIN(", ";1;A1:A3) | Apple, Banana, Cherry |
| PROPER | Capitalizes first letter of each word | =PROPER("welcome to quitexams") | Welcome To Quitexams |
| LOWER | Converts to lowercase | =LOWER("HELLO") | hello |
| UPPER | Converts to uppercase | =UPPER("world") | WORLD |
| REPT | Repeats text | =REPT("*";10) | ********** |
Text functions simplify data preparation by allowing flexible text transformation, case conversions, and joining operations. These are especially useful in reports, form templates, and data cleaning tasks.
Login with Google