# REPLACE Function

Replaces part of a text string with a different text string

Over 2 million + professionals use CFI to learn accounting, financial analysis, modeling and more. Unlock the essentials of corporate finance with our free resources and get an exclusive sneak peek at the first module of each course.

The REPLACE function in Excel replaces part of a text string with a different text string, useful for modifying variable text data. Its syntax is =REPLACE(old_text, start_num, num_chars, new_text). The function is particularly helpful in financial analysis to clean and adjust text data. It addresses errors such as negative or non-numeric arguments. To ensure proper use, convert date, time, or numeric values to text before applying the function.

## What is the REPLACE Function?

The REPLACE Function[1] is categorized under Excel TEXT functions. The function will replace part of a text string, based on the number of characters you specify, with a different text string.

In financial analysis, the REPLACE function can be useful if we wish to remove text from a cell when the text is in a variable position.

### Formula

=REPLACE(old_text, start_num, num_chars, new_text)

The REPLACE function uses the following arguments:

1. Old_text (required argument) – This is the text we wish to replace some characters.
2. Start_num (required argument) – The position, within old_text, of the first character that you want to replace.
3. Num_chars (required argument) – This is the number of characters to replace.
4. New_text (required argument) – The replacement text.

### How to use the REPLACE Function in Excel?

As a worksheet function, the REPLACE function can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let us consider an example:

#### Example 1

Suppose we imported data from an external source and we wish to find certain characters. The data given are:

The formula and results in Excel are given in the screenshot below:

### A few notes about the REPLACE Function:

1. #VALUE error – Occurs when:

a. The given start_num argument is negative or is a non-numeric value.

b. The given num_chars argument is negative or non-numeric.

2. The REPLACE function is designed for use with text strings and returns a text string. Therefore, if we attempt to use the function with a date, time, or number, we may get odd results. If we’re not planning to use date, time, or number in further calculations, we can solve the problem by converting the values into text using Excel’s Text To Columns tool. To do it:

• We need to select the cell(s) we want to convert to text. The selected text must not span more than one column.
• From the Data tab of the Excel ribbon, select the Text to Columns, as shown below:

• Ensure the Delimited option is selected, then click Next.

• Ensure all the delimiter options are unselected and then click Next again. Select Text and click the Finish button.

The REPLACE function should now work as expected on the values that were converted to text.

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:

### Excel Tutorial

To master the art of Excel, check out CFI’s Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

Launch CFI’s Excel Course now

to take your career to the next level and move up the ladder!

0 search results for ‘