MID Function (LibreOffice Calc)
The MID function in LibreOffice Calc extracts a substring from the middle of a text string, starting at a specified position and returning a specified number of characters. It is essential for parsing structured text, codes, and variable‑length fields.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the MID Function Does ▾
- Extracts characters starting at a specific position
- Works with text, numbers (converted to text), and formulas
- Useful for parsing codes, identifiers, and delimited text
- Supports variable‑length extraction
It is designed to be precise, flexible, and widely compatible.
Syntax ▾
MID(text; start; num_chars)
Arguments
-
text:
The text string to extract from. -
start:
The position (1‑based) where extraction begins. -
num_chars:
Number of characters to extract.
Basic Examples ▾
Extract characters starting at position 2
=MID("Hello"; 2; 3)
Returns "ell".
Extract from a cell
=MID(A1; 4; 2)
Extract from a number (converted to text)
=MID(2024; 2; 2)
Returns "02".
Extract a single character
=MID("ABC"; 2; 1)
Returns "B".
Advanced Examples ▾
Extract the middle word from a three‑word string
=MID(A1; FIND(" "; A1) + 1; FIND(" "; A1; FIND(" "; A1) + 1) - FIND(" "; A1) - 1)
Extract text between two delimiters
=MID(A1; FIND("["; A1) + 1; FIND("]"; A1) - FIND("["; A1) - 1)
Extract variable‑length codes
=MID(A1; 3; LEN(A1) - 5)
Extract characters after the first dash
=MID(A1; FIND("-"; A1) + 1; LEN(A1))
Extract characters between the 2nd and 3rd spaces
=MID(A1; FIND("☼"; SUBSTITUTE(A1; " "; "☼"; 2)) + 1; FIND("☼"; SUBSTITUTE(A1; " "; "☼"; 3)) - FIND("☼"; SUBSTITUTE(A1; " "; "☼"; 2)) - 1)
Extract trailing digits from mixed text
=MID(A1; MATCH(TRUE; ISNUMBER(VALUE(MID(A1; ROW(INDIRECT("1:" & LEN(A1))); 1))); 0); LEN(A1))
Extract a substring dynamically
=MID(A1; B1; C1)
Where B1 = start, C1 = length.
Edge Cases and Behavior Details ▾
start < 1 → Err:502
=MID("ABC"; 0; 2)
num_chars < 0 → Err:502
=MID("ABC"; 2; -1)
start beyond text length → returns ""
=MID("ABC"; 10; 5) → ""
num_chars larger than remaining text → returns remaining text
=MID("ABC"; 2; 10) → "BC"
text is a number → converted to text
=MID(12345; 2; 3) → "234"
text is empty
=MID(""); 1; 5 → ""
text is an error → error propagates
=MID(#N/A; 2; 3) → #N/A
MID respects Unicode character boundaries
But combining marks may behave unexpectedly.
Common Errors and Fixes ▾
Err:502 — Invalid argument
Occurs when:
- start < 1
- num_chars < 0
- start or num_chars is non‑numeric
MID returns unexpected characters
Cause:
- Hidden characters
- Unicode combining marks
- Leading/trailing spaces
Fix:
Use TRIM(A1) or CLEAN(A1).
MID returns fewer characters than expected
Cause:
- start too close to end
- num_chars exceeds remaining length
Best Practices ▾
- Use MID for structured text parsing and variable‑length extraction
- Combine with FIND/SEARCH for delimiter‑based extraction
- Use TRIM to clean input before slicing
- Use LEN to validate expected lengths
- Use MID with LEFT/RIGHT for full string decomposition
Related Patterns and Alternatives ▾
- Use LEFT to extract from the beginning
- Use RIGHT to extract from the end
- Use FIND or SEARCH to locate delimiters
- Use LEN to measure text length
- Use SUBSTITUTE to target specific occurrences
By mastering MID and its companion functions, you can build powerful, precise, and flexible text‑processing workflows in LibreOffice Calc.