Extract Substrings in LibreOffice Calc with LEFT, RIGHT, MID
LibreOffice Calc provides powerful text functions to manipulate and
extract specific segments of text from cells. This guide explains how to
use the LEFT, RIGHT, and MID
functions to extract substrings from the beginning, end, or middle of
any text string, complete with syntax breakdowns and clear examples.
1. The LEFT Function
The LEFT function extracts a specified number of
characters starting from the beginning (far left) of a text string.
Syntax
=LEFT(text, [number])text: The string or cell reference containing the text you want to extract from.number(optional): The number of characters to extract. If omitted, the default value is1.
Example
If cell A1 contains CALC-2024-DATA:
=LEFT(A1, 4)Result: CALC
2. The RIGHT Function
The RIGHT function extracts a specified number of
characters starting from the end (far right) of a text string.
Syntax
=RIGHT(text, [number])text: The string or cell reference containing the text you want to extract from.number(optional): The number of characters to extract. If omitted, the default value is1.
Example
If cell A1 contains CALC-2024-DATA:
=RIGHT(A1, 4)Result: DATA
3. The MID Function
The MID function extracts a specific number of
characters from anywhere inside a text string, starting at the position
you define.
Syntax
=MID(text, start_position, number)text: The string or cell reference containing the text.start_position: The numeric position of the first character you want to extract (the first character of the string is position1).number: The total number of characters to extract from the starting position.
Example
If cell A1 contains CALC-2024-DATA:
=MID(A1, 6, 4)Result: 2024
Combining with Dynamic Search (Advanced Usage)
When the text length varies, you can pair these formulas with
SEARCH or FIND to extract text
dynamically.
Example: Extract text before a hyphen
If cell A1 contains Project-Report:
=LEFT(A1, SEARCH("-", A1) - 1)Result: Project