The REPLACE function
Changing the characters at a given position: masking the middle of a number, inserting a hyphen, fixing a prefix.
Syntax
=REPLACE(old_text, start_num, num_chars, new_text)
In a French Excel: =REMPLACER(ancien_texte; no_départ; no_car; nouveau_texte).
Arguments
- old_text: the cell.
- start_num: the position of the first character to replace.
- num_chars: how many characters; 0 to insert without removing anything.
- new_text: what replaces them.
An example
=REPLACE(A2,5,LEN(A2)-8,REPT("*",LEN(A2)-8))
Masks a number, leaving only its first four and last four characters visible.
Pitfalls
- A number is first read as text, without its leading zeros.
- To replace a word wherever it is, use SUBSTITUTE.
Going further
Frequently asked questions
- How do I insert a character in the middle of a text?
=REPLACE(A2,3,0,"-")inserts a hyphen after the 2nd character.