REPLACE Function (LibreOffice Calc)
The REPLACE function in LibreOffice Calc substitutes part of a text string based on position and length. It is essential for structured text editing, code manipulation, and controlled string transformations.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the REPLACE Function Does ▾
- Replaces characters starting at a specific position
- Removes or inserts text by controlling length
- Works with text, numbers (converted to text), and formulas
- Ideal for structured, positional editing
It is designed to be precise, predictable, and essential for fixed‑format text manipulation.
Syntax ▾
REPLACE(text; start; num_chars; new_text)
Arguments
-
text:
The original text string. -
start:
The position (1‑based) where replacement begins. -
num_chars:
Number of characters to remove. -
new_text:
The text to insert in place of the removed characters.
Basic Examples ▾
Replace 3 characters starting at position 2
=REPLACE("Hello"; 2; 3; "i")
Returns "Hio".
Insert text without removing characters
=REPLACE("Hello"; 2; 0; "X")
Returns "HXello".
Remove characters without inserting anything
=REPLACE("Hello"; 2; 3; "")
Returns "Ho".
Replace part of a number (converted to text)
=REPLACE(2024; 3; 2; "99")
Returns "2099".
Advanced Examples ▾
Mask part of an ID
=REPLACE(A1; 4; 4; "****")
Replace file extension
=REPLACE(A1; FIND("."; A1) + 1; LEN(A1); "txt")
Replace characters after a delimiter
=REPLACE(A1; FIND("-"; A1) + 1; LEN(A1); "NEW")
Replace the last N characters
=REPLACE(A1; LEN(A1)-2; 3; "XYZ")
Insert text at the end
=REPLACE(A1; LEN(A1)+1; 0; "_END")
Replace middle word in a three‑word string
=REPLACE(A1; FIND(" "; A1) + 1; FIND(" "; A1; FIND(" "; A1) + 1) - FIND(" "; A1) - 1; "NEW")
Replace characters dynamically
=REPLACE(A1; B1; C1; D1)
Where B1 = start, C1 = length, D1 = replacement.
Replace only if delimiter exists
=IF(ISERROR(FIND("-"; A1)); A1; REPLACE(A1; FIND("-"; A1)+1; 99; "OK"))
Replace characters in fixed‑width data
=REPLACE(A1; 6; 2; "00")
Edge Cases and Behavior Details ▾
start < 1 → Err:502
=REPLACE("ABC"; 0; 1; "X")
num_chars < 0 → Err:502
=REPLACE("ABC"; 2; -1; "X")
start beyond text length inserts at end
=REPLACE("ABC"; 10; 2; "X") → "ABCX"
num_chars larger than remaining text removes to end
=REPLACE("ABC"; 2; 10; "X") → "AX"
text is a number → converted to text
=REPLACE(12345; 2; 2; "00") → "10005"
text is empty
=REPLACE(""); 1; 1; "X" → "X"
text is an error → error propagates
=REPLACE(#N/A; 2; 3; "X") → #N/A
new_text may be empty
=REPLACE("ABC"; 2; 1; "") → "AC"
Common Errors and Fixes ▾
Err:502 — Invalid argument
Occurs when:
- start < 1
- num_chars < 0
- start or num_chars is non‑numeric
REPLACE returns unexpected output
Cause:
- Off‑by‑one start position
- num_chars too large
- Hidden characters in text
Fix:
Use LEN, TRIM, CLEAN to inspect and normalize.
REPLACE inserts at wrong position
Cause:
- start beyond text length
- start miscalculated from FIND/SEARCH
Best Practices ▾
- Use REPLACE for positional editing and fixed‑width text
- Use SUBSTITUTE when replacing matching text, not positions
- Combine with FIND/SEARCH for delimiter‑based replacement
- Use LEN to calculate dynamic start/length values
- Use TRIM and CLEAN to normalize text before replacement
Related Patterns and Alternatives ▾
- Use SUBSTITUTE to replace matching text
- Use LEFT, RIGHT, MID for extraction
- Use FIND and SEARCH to locate positions
- Use LEN to calculate dynamic ranges
- Use TEXTJOIN or CONCAT for reconstruction
By mastering REPLACE and its companion functions, you can build powerful, structured, and highly controlled text‑processing workflows in LibreOffice Calc.