Contact
12 programs
Explore the site

MID Function (LibreOffice Calc)

Text Beginner LibreOffice Calc LibreOffice 3.0

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.

Verification status: Selected examples were executed in LibreOffice Calc 25.2.3.2. This does not establish that every formula and edge case was tested. How formula examples are checked

The **MID** function extracts a substring from the middle of a text string in LibreOffice Calc. You specify the starting position and the number of characters to return. MID is essential for parsing structured text, extracting variable‑length fields, and working with codes, identifiers, and delimited data.

Native-tested visual example: Extract four characters from an identifier

The code INV-2026-0042 contains a four-digit year beginning at character position five.

Enter this formula in cell E5:

=MID(A5;5;4)

Expected result: 2026. MID starts counting at 1; positions 5–8 are 2026. The result is text, not a numeric year.

LibreOffice Calc 25.2 actual screenshot of the MID function with source cells, the formula in the formula bar, and result Extracted year: 2026

Real screenshot captured from LibreOffice Calc 25.2.3.2. The selected E5 cell shows the calculated result and the formula bar displays the entered formula.

Download the editable MID example (.ods) — change the sample input cells and watch the calculation update.

Why it works: MID starts counting at 1; positions 5–8 are 2026. The result is text, not a numeric year. Watch for: MID uses character positions, so codes with inconsistent prefixes need a different parsing strategy.

Verification: Example calculated in LibreOffice Calc 25.2.3.2. Screenshot is a genuine application capture, not a mockup.

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
MID is the most flexible extraction tool — perfect for slicing text anywhere inside a string, especially when combined with FIND and LEN.
  • 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.

Reproducible example (tested in LibreOffice Calc)

This standalone example was calculated in LibreOffice Calc 25.2.3.2 and matched the expected value. It is a practical cross-check of one usage, not proof that every example elsewhere on this page is correct.

=MID("Spreadsheet";7;5)

Expected result: sheet.

Extract a portion of text beginning at a specific position.

If the result differs, check the referenced cells, argument separators and number formatting. For more information, consult the LibreOffice Calc official help.