Excel REPLACE: character positions, suffixes and SUBSTITUTE

REPLACE changes part of a string using a starting position and a count of characters. Its Polish name is ZASTĄP. I choose it when the layout is known, such as a product code whose fifth through eighth characters identify a series.

If the requirement is to find particular text regardless of position, use SUBSTITUTE, called PODSTAW in Polish. The earlier Polish entry confused those operations. Both language versions here now describe positional replacement and its four arguments.

Four required arguments

=REPLACE(old_text,start_num,num_chars,new_text)

The first argument is a string or cell reference. The second identifies the first character to replace, counting from one. The third is the number of characters removed. The fourth is the replacement string, which can have a different length from the removed section.

The Microsoft REPLACE reference defines this syntax for releases including Microsoft 365 and Excel 2016, 2019, 2021 and 2024. Examples assume English names and argument commas. Typical Polish notation uses =ZASTĄP(stary_tekst;nr_poz_pocz;liczba_znaków;nowy_tekst).

Replace a middle code segment

In the synthetic code ABCD1234XYZ, the first four characters are ABCD, the next four are 1234, and the suffix is XYZ. I want to change only the middle section to 0000.

=REPLACE("ABCD1234XYZ",5,4,"0000")

The result is ABCD0000XYZ. Starting at position five, the formula removes four characters and inserts the new segment. The suffix remains unchanged. If the code is stored in A2, use =REPLACE(A2,5,4,"0000") instead of a literal string.

There is no need to add MID when the start and length are already known. Extracting a substring does not turn the positional argument into a content search. Each argument must retain the role specified in the function’s syntax.

The replacement length may differ

Consider Jan Kowalski. The first four characters contain the first name and a space, so the surname begins at position five. Kowalski contains eight characters.

=REPLACE("Jan Kowalski",5,8,"Nowak")

The output is Jan Nowak. Eight characters were removed and five inserted. Excel does not add three spaces to maintain the original width. A fixed width export would need a separate rule for padding.

Spaces and hyphens also occupy positions. In 123-456-789, the middle three digits start at position five. Therefore =REPLACE("123-456-789",5,3,"XXX") returns 123-XXX-789. This is a synthetic code example rather than personal data.

Last three characters: length minus two

For text of varying length in A2, the start of the final three characters is its length minus two. ExcelFunction has thirteen characters, so the starting position is eleven.

=REPLACE(A2,LEN(A2)-2,3,"XYZ")

With ExcelFunction in A2, the result is ExcelFunctXYZ. The earlier English example subtracted three, started at position ten and left the final n. With positions counted from one, a trailing section of length k starts at n-k+1.

This expression assumes at least three characters. Define a policy for shorter codes rather than assuming the segment exists. The LEN reference also discusses spaces and compatibility differences for complex Unicode characters. These examples use simple letters and digits.

REPLACE and SUBSTITUTE on identical input

For AA-BB-AA, replacing the first two characters with ZZ produces ZZ-BB-AA. The location is specified directly:

=REPLACE("AA-BB-AA",1,2,"ZZ")

To replace every occurrence of AA, use =SUBSTITUTE("AA-BB-AA","AA","ZZ"), producing ZZ-BB-ZZ. SUBSTITUTE’s optional fourth argument can select an occurrence. REPLACE’s fourth argument always supplies the replacement text.

The SUBSTITUTE reference explains the distinction. See my content replacement guide for additional examples. Similar everyday descriptions of the operation do not imply identical function arguments.

The code layout must actually be stable

Position five makes sense only while the source follows the assumed layout. An extra space, variable prefix length or different separator can shift the segment. The function may still produce a valid string while editing the wrong characters.

Before filling down a column, I inspect short and long codes and examples containing spaces. I keep the original in another column. After the edit, I compare the prefix and suffix to confirm that both remain intact.

If the layout varies, establish how to locate the target segment first. Searching for a delimiter or using SUBSTITUTE may be more appropriate. A single hardcoded position is not a general repair method for arbitrary text. The broader data cleaning guide discusses preserving meaning while changing data.

Exercise: predict both unchanged sides

For ABCD1234XYZ, replace four characters starting at position five with Q. The expected result is ABCDQXYZ. Then insert 987654 in place of that same original segment, giving ABCD987654XYZ. The middle changes length while both outer sections remain.

Finally, add a space at the beginning of the source and explain why position five no longer identifies the first digit. That case checks an assumption about the input format which correct function syntax cannot guarantee on its own.