How to replace character in Excel by position

by | Jul 11, 2018 | Excel Tips & Tricks

How to replace a character by its position

This article shows how to replace character in Excel by position using REPLACE function. If you are looking to replace a specific character please refer to article How to replace a character.

Syntax

=REPLACE( text, start position of replacement, number of characters to be replaced, replacement text)

Steps

  1. Start with =REPLACE( function
  2. Type or select the range that contains your text B2,
  3. Continue with the position that replacement start 10,
  4. Next enter the number of characters after the start position 4,
  5. Enter the text to replace with "XXXX"
  6. Type ) to close function and finish the formula

How

The REPLACE function, replaces certain number of characters which starts from specified index. The function gets 4 arguments:

  • The text to replace.
  • The starting location in the text to search.
  • The number of characters to replace.
  • The text to replace with.

The REPLACE function is suitable for replacing text of known length and positioning. The number of characters to replace may be different than the character count of new text. This means you can replace 4 characters with a single character.

=REPLACE(B2,10,4,"XXXX")

Alternatively, there is REPLACEB function to replace character in Excel based on bytes instead of characters.