Excel REPLACE and REPLACEB: Replace text by position
Excel’s REPLACE function lets you choose where to start, how many characters to remove, and what to put in their place. For example, replacing two characters starting at position 4 in AB-CD-123 with XY gives AB-XY-123. Use SUBSTITUTE when you know the text to replace and its position may vary.
Supply the four REPLACE arguments
=REPLACE(old_text,start_num,num_chars,new_text)
Enter the original text, starting position, number of characters to remove, and replacement text in that order. The first character is at position 1. The replacement can have a different length from the text it replaces. The formula returns new text and leaves the source cell as it was.
Microsoft’s REPLACE syntax and compatibility notes
Replace, insert, or delete characters in the same cell
Enter AB-CD-123 in A2. Put each formula in a separate blank cell. Compare how one replaces two characters starting at C, while another inserts text at the same position without removing anything. To delete a character, supply an empty string as the replacement.
| Formula | Result | Operation |
|---|---|---|
=REPLACE(A2,4,2,"XY") |
AB-XY-123 | Replace CD with XY starting at the fourth character |
=REPLACE(A2,4,0,"NEW-") |
AB-NEW-CD-123 | Insert NEW- before the fourth character |
=REPLACE(A2,3,1,"") |
ABCD-123 | Delete the hyphen at position 3 |
If you get an error, check for a starting position below 1 or a negative character count. When segment lengths vary, check the delimiter positions before applying a fixed offset. Use SUBSTITUTE if the goal is to find and change particular text.
Microsoft’s instructions for SUBSTITUTE
Review older REPLACEB formulas
Microsoft lists REPLACEB as deprecated. Where a language using a double-byte character set is the default editing language, REPLACEB can count those characters as two. This is separate from the byte length in a UTF-8 file.
Use REPLACE in new formulas that work with character positions. Before changing an older workbook, compare the outputs with representative data such as Korean or Japanese text. Check emoji separately, as character position can also depend on the workbook’s compatibility setting.
Microsoft Learn’s explanation of how REPLACEB counts characters
Share masked results
Enter EMP-4821 in A6 and apply the formula below to get EMP-****. For sharing, paste only the required results as values into a new file, then check the file for original codes or formulas referring to them. A formula that masks part of a code leaves the original in the source workbook.
=REPLACE(A6,5,4,"****")




