Excel REPLACE/REPLACEB Function

Replace or Add Characters to Data with Excel's REPLACE Function

Replace or Add Characters to Data with Excel's REPLACE Function
Replace or Add Characters to Data with Excel's REPLACE Function. © Ted Fench

Use Excel's REPLACE function to replace unwanted text data in a worksheet cell with good data or with nothing at all.

Imported or copied data sometimes includes unwanted characters or words along with the good data. The REPLACE function is one way to quickly correct this situation as shown in the example in the image above.

This is especially true when long columns of imported data need correcting since it is possible to use the fill handle or copy and paste to copy the REPLACE function to multiple cells in the worksheet.

The types of text data that the function can replace includes:

  • formatting characters -  row two above;
  • punctuation marks - the hyphen added to the phone number - row four above;
  • letters of the alphabet - row six above.

The function can also be used to simply remove unwanted characters by replacing it with nothing - row three above.

The REPLACE Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the REPLACE function is:

=REPLACE(Old_text, Start_num, Num_chars, New_text)

Old_text - (required) the piece of data to be changed. This argument can be:

  • the actual data to be changed - either text or number data;
  • a cell reference to the location of the data in a worksheet.

Start_num - (required) specifies the start position - from the left - of the characters in Old_text to be replaced.

Num_chars - (required) specifies the number of characters to be replaced after Start_num.

 If blank, the function assumes that no characters are to be replaced and adds the characters specified in the New_text argument - row three above.

New_text - (required) specifies the new data to be added. If blank, the function assumes that no characters are to be added and just removes the characters specified for the Num_chars argument - row four above.

 #NAME? and #VALUE! Errors

 #NAME? - Occurs if text data entered as the Old_text argument is not enclosed in double quotation marks - row five above.

#VALUE!    - Occurs if the Start_num or Num_chars arguments are negative or contain non-numeric values - row eight above.

REPLACE and Calculation Errors

When using the REPLACE function with numbers - as outlined in the steps below - the formula results ($24,398) are treated as text data by Excel and may return incorrect results if used in calculations.

REPLACE vs. REPLACEB

Identical to the REPLACE function in purpose and syntax is REPLACEB.

According to Excel's help file, the only difference between the two is the group of languages that each is intended to support. 

REPLACEB - for use with versions of Excel utilizing double-byte character set languages - such as Japanese, Chinese (Simplified), Chinese (Traditional), and Korean.

REPLACE - for use in versions of Excel using single-byte character set languages - such as English and other western languages.

Example Using Excel's REPLACE Function

This example covers the steps used to enter the REPLACE function into cell C5 in the image to replace the first three characters of the text string ^,398 with a dollar sign ($) to get $24,398.

Options for entering the REPLACE function include manually typing in the entire formula:

 =REPLACE(A5,1,3,"$"),

or using the function's dialog box - as outlined below.

Although possible to manually enter the function and its arguments, it is often easier to use the dialog box as it takes care of the function's syntax - such as brackets and comma separators between arguments.

  1. Click on cell C5 in the worksheet to make it the active cell;
  2. Click on the Formulas tab of the ribbon menu;
  3. Choose Text from the ribbon to open the function drop down list;
  4. Click on REPLACE in the list to bring up the function's dialog box;
  5. In the dialog box, click on the Old_text line;
  6. Click on cell A5 in the worksheet to enter that cell reference for the Old_text argument;
  7. Click on the Start_num line;
  8. Type the number 1  - starts the replacement from the first character on the left
  1. Click on the Num_chars line;
  2. Type the number  3 on this line - the first three characters will be replaced;
  3. Click on the New_text line;
  4. Type a dollar sign ( $ ) - adds the dollar sign to the front of 24,398;
  5. Click OK to close the dialog box and return to the worksheet
  6. The amount $24,398 should appear in cell C5
  7. When you click on cell C5 the complete function =REPLACE(A5,1,3,"$") appears in the formula bar above the worksheet

The REPLACE Function and Paste Value

REPLACE and Excel's other text functions are designed to leave the original data in one cell with the edited text placed in another. 

Doing so keeps the original data intact for future use or makes it possible to correct any problems that occur during editing.

At times, however, it may be preferable to remove the original data and just keep the edited version.

To do this, combine the REPLACE function's output with paste value - which is part of Excel's paste special feature.

The result of doing so is that the values will still be present, but the original data and the REPLACE function can be deleted - leaving just the corrected data.