Using REPLACE and SUBSTITUTE functions in Excel - formula examples

The tutorial explains the Excel REPLACE and SUBSTITUTE functions with examples of uses. See how to use the REPLACE function with text strings, numbers and dates, and how to nest several REPLACE or SUBSTITUTE functions within one formula.

Last week we discussed various ways of using FIND and SEARCH functions within your Excel worksheets. Today, we will be taking a deeper look at two other functions to replace text in a cell based on its location or substitute one text string with another based on content. As you may have guessed, I am talking about the Excel REPLACE and SUBSTITUTE functions.

Excel REPLACE function

The REPLACE function in Excel allows you to swap one or several characters in a text string with another character or a set of characters.

REPLACE(old_text, start_num, num_chars, new_text)

As you see, the Excel REPLACE function has 4 arguments, all of which are required.

  • Old_text - the original text (or a reference to a cell with the original text) in which you want to replace some characters.
  • Start_num - the position of the first character within old_text that you want to replace.
  • Num_chars - the number of characters you want to replace.
  • New_text - the replacement text.

For example, to change the word "sun" to "son", you can use the following formula:

=REPLACE("sun", 2, 1, "o")

And if you put the original word in some cell, say A2, you can supply the corresponding cell reference in the old_text argument:

=REPLACE(A2, 2, 1, "o")
Excel REPLACE function

Note. If the start_num or num_chars argument is negative or non-numeric, an Excel Replace formula returns the #VALUE! error.

Using Excel REPLACE function with numeric values

The REPLACE function in Excel is designed to work with text strings. Of course, you can use it to replace numeric characters that are part of a text string, for example:

=REPLACE(A2, 7, 4, "2016")
Replacing numeric characters that are part of a text string

Notice that we enclose "2016" in double quotes as you usually do with text values.

In a similar manner, you can replace one or more digits within a number. For example:

=REPLACE(A4, 4, 4,"6")

And again, you have to enclose the replacement value in double quotes ("6").
Be sure to enclose the replacement value in double quotes.

Note. An Excel REPLACE formula always returns a text string, not number. In the screenshot above, notice the left alignment of the returned text value in B2, and compare it to the right-aligned original number in A2. And because it's a text value you won't be able to use it in other calculations unless you convert it back to number, for example by multiplying by 1 or by using any other method described in How to convert text to number.

Using Excel REPLACE function with dates

As you have just seen, the REPLACE function works fine with numbers, except that it returns a text string :) Remembering that in the internal Excel system, dates are stored as numbers, you may try to use some Replace formulas on dates. Results would be quite embarrassing.

For instance, you have a date in A2, say 1-Oct-14, and you want to change "Oct" to "Nov". So, you write the formula REPLACE(A2, 4, 3, "Nov") that tells Excel to replace 3 chars in cells A2 beginning with the 4th char… and got the following result:
A wrong way to use the REPLACE function on dates

Why's that? Because "01-Oct-14" is only a visual representation of the underlying serial number (41913) that represents the date. So, our Replace formula changes the last 3 digits in the above serial number to "Nov" and returns the text string "419Nov".

To get the Excel REPLACE function to correctly work with dates, you can convert dates to text strings first by using the TEXT function or any other technique demonstrated in How to convert date to text in Excel. Alternatively, you can embed the TEXT function directly in the old_text argument of the REPLACE function:

=REPLACE(TEXT(A2, "dd-mmm-yy"), 4, 3, "Nov")
The right way to use the REPLACE function on dates

Please remember that the result of the above formula is a text string, and therefore this solution works only if you are not planning to use the modified dates in further calculations. If you do need dates rather than text strings, use the DATEVALUE function to turn the values returned by the Excel REPLACE function back to dates:

=DATEVALUE(REPLACE(TEXT(A2, "dd-mmm-yy"), 4, 3, "Nov"))

Nested REPLACE functions to do multiple replacements in a cell

Quite often, you may need to do more than one replacement in the same cell. Of course, you could do one replacement, output an intermediate result into an additional column, and then use the REPLACE function again. However, a better and more professional way is to use nested REPLACE functions that let you perform several replacements with a single formula. In this context, "nesting" means placing one function within another.

Consider the following example. Supposing you have a list of telephone numbers in column A formatted as "123456789" and you want to make them look more like phone numbers by adding hyphens. In other words, your goal is to turn "123456789" into "123-456-789".

Inserting the first hyphen is easy. You write a usual Excel Replace formula that replaces zero characters with a hyphen, i.e. adds a hyphen in the 4th position in a cell:

=REPLACE(A2,4,0,"-")

The result of the above Replace formula is as follows:
The REPLACE formula to add a hyphen in the 4th position in a cell

Okay, and now we need to insert one more hyphen in the 8th position. To do this, you place the above formula within another Excel REPLACE function. More precisely, you embed it in the old_text argument of the other function, so that the second REPLACE function will handle the value returned by the first REPLACE, and not the value in cell A2:

=REPLACE(REPLACE(A2,4,0,"-"),8,0,"-")

As the result, you get the phone numbers in the desired formatting:
Using nested REPLACE functions in Excel

In a similar manner, you can use nested REPLACE functions to make text strings look like dates by adding a forward slash (/) where appropriate:

=(REPLACE(REPLACE(A2,3,0,"/"),6,0,"/"))
A nested REPLACE formula to make text strings look like dates

Moreover, you can convert text strings into real dates by wrapping the above REPLACE formula with the DATEVALUE function:

=DATEVALUE(REPLACE(REPLACE(A2,3,0,"/"),6,0,"/"))

And naturally, you are not limited in the number of functions you can nest within one formula (the modern versions of Excel 2010, 2013 and 2016 allow up to 8192 characters and up to 64 nested functions in a formula).

For example, you can use 3 nested REPLACE functions to have a number in A2 appear like date and time:

=REPLACE(REPLACE(REPLACE(REPLACE(A2,3,0,"/") ,6,0,"/"), 9,0, " "), 12,0, ":")
The nested REPLACE functions make a number look like date and time

Replacing a string that appears in a different position in each cell

So far, in all the examples we have been dealing with values of a similar nature and have made replacements in the same position in each cell. But real-life tasks are often more complicated than that. In your worksheets, the characters to be replaced may not necessarily appear in the same place in each cell, and therefore you will have to find the position of the first character that should be replaced. The following example will demonstrate what I'm talking about.

Supposing you have a list of email addressing in column A. And the name of one company has changed from "ABC" to, say, "BCA". So, you have to update all of the clients' email addressing accordingly.

But the problem is that the client names are of different length, and that is why you cannot specify exactly where the company name begins. In other words, you do not know what value to supply in the start_num argument of the Excel REPLACE function. To find it out, use the Excel FIND function to determine the position of the first char in the string "@abc":

=FIND("@abc",A2)

And then, supply the above FIND function in the start_num argument of your REPLACE formula:

=REPLACE(A2, FIND("@abc",A2), 4, "@bca")

Tip. We include "@" in our Excel Find and Replace formula to avoid accidental replacements in the name part of email addresses. Of course, there's a very slim chance that such matches will occur, and still you may want to be on the safe side.

As you see in the following screenshot, the formula has no problem with finding and replacing the old text with the new one. However, if the text string to be replaced is not found, the formula returns the #VALUE! error:
The Excel FIND and REPLACE formula to change the domain name in email addresses

And we want the formula to return the original email address instead of the error. So, let's enclose our FIND & REPLACE formula in the IFERROR function:

=IFERROR(REPLACE(A2, FIND("@abc",A2), 4, "@bca"),A2)

And this improved formula works perfectly, doesn't it?
The improved FIND / REPLACE formula

Another practical application of the REPLACE function is to capitalize the first letter in a cell. Whenever you deal with a list of names, products, and the like, you can use the above-linked formula to change the first letter to UPPERCASE.

Tip. If you want to make the replacements in the original data, an easier way would be using the Excel FIND and REPLACE dialog.

Excel SUBSTITUTE function

The SUBSTITUTE function in Excel replaces one or more instances of a given character or text string with a specified character(s).

The syntax of the Excel SUBSTITUTE function is as follows:

SUBSTITUTE(text, old_text, new_text, [instance_num])

The first three arguments are required and the last one is optional.

  • Text - the original text in which you want to substitute characters. Can be supplied as a test string, cell reference, or a result of another formula.
  • Old_text - the character(s) you want to replace.
  • New_text - the new character(s) to replace old_text with.
  • Instance_num - the occurrence of old_text you want to replace. If omitted, every occurrence of the old text will be changed to the new text.

For example, all of the below formulas substitute "1" with "2" in cell A2, but return different results depending on which number you supply in the last argument:

=SUBSTITUTE(A2, "1", "2", 1) - Substitutes the first occurrence of "1" with "2".

=SUBSTITUTE(A2, "1", "2", 2) - Substitutes the second occurrence of "1" with "2".

=SUBSTITUTE(A2, "1", "2") - Substitutes all occurrences of "1" with "2".
Excel SUBSTITUTE function

In practice, the SUBSTITUTE function is also used for removing unwanted characters from cells. For real-life examples, please see:

Note. The SUBSTITUTE function in Excel is case-sensitive. For example, the following formula replaces all instances of the uppercase "X" with "Y" in cell A2, but it won't replace any instances of the lowercase "x".

A case-sensitive Excel SUBSTITUTE formula

Substitute multiple values with a single formula (nested SUBSTITUTE)

As is the case with the Excel REPLACE function, you can nest several SUBSTITUTE functions within a single formula to do several substitutions at a time, i.e. substitute several characters or substrings with a single formula.

Supposing you have a text string like "PR1, ML1, T1" in cell A2, where "PR" stands for "Project, "ML" stands for "Milestone" and "T" means "Task". What you want is to replace the three codes with full names. To achieve this, you can write 3 different SUBSTITUTE formulas:

=SUBSTITUTE(A2,"PR", "Project ")

=SUBSTITUTE(A2, "ML", "Milestone ")

=SUBSTITUTE(A2, "T", "Task ")

And then nest them into each other:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"PR","Project "),"ML","Milestone "),"T","Task ")

Notice that we've added a space at the end of each new_text argument for better readability.
Using nested SUBSTITUTE functions in Excel

To learn other ways to replace multiple values at a time, please see How to do mass find and replace in Excel.

Excel REPLACE vs. Excel SUBSTITUTE

The Excel REPLACE and SUBSTITUTE functions are very similar to each other in that both are designed to swap text strings. The differences between the two functions are as follows:

  • SUBSTITUTE replaces one or more instances of a given character or a text string. So, if you know the text to be replaced, use the Excel SUBSTITUTE function.
  • REPLACE changes characters in a specified position of a text string. So, if you know the position of the character(s) to be replaced, use the Excel REPLACE function.
  • The SUBSTITUTE function in Excel allows adding an optional parameter (instance_num) that specifies which occurrence of old_text should be changed to new_text.

This is how you use the SUBSTITUTE and REPLACE functions in Excel. Hopefully, these examples will prove useful in solving your tasks. I thank you for reading and hope to see on our blog next week!

Download practice workbook

REPLACE and SUBSTITUTE formula examples (.xlsx file)

Table of contents

301 comments

  1. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A","B"),"B","A"),"C","Z"),"Z","C")

    can someone please help me on this.

    results: "B" is replaced with "A"
    "Z" is replaced with "C"

    But "A" isn't replaced it stays the same and neither does "C".

    • I guess u should use if function rather then subsitute. because this funtion is working as first come first serve basis.

  2. HOW TO REPLACE ALL CELLS
    original replace
    307(306) 307
    304(305) 304
    300(303) 303

    AT SINGLE TIME HOW TO REPLACE PLEASE SUGGEST

    • =left(a2,find("(",a2)-1)

  3. I have a giant column that lists various dates and includes a time.
    I want to delete the comma and the time- leaving only the date.

    example:
    10/15/12, 5:21 PM
    I want to change to:
    10/15/12

    or in the same column:
    1/4/13, 5:40 PM
    I want to change to:
    1/4/13

    I ultimately want to sort the column by date. But without removing the comma and time I cannot get the column to sort by date correctly. Currently the years do not descend correctly.

    Thanks in advance for any help!

    • Hello,

      Please try to do the following:

      - Select the column with the data you want to modify;
      - Press Ctrl + F and go to the “Replace” tab;
      - Enter “,*” in the “Find what:” field, and leave the ”Replace with:” field blank;
      - Click on the “Replace All” button.

      Hope this will help you.

      • if in cell a2=1/04/2018 apply this formula
        =left(a2,find(",",a2)-1)

        • if in cell a2=1/04/2018,05:14 apply this formula
          =left(a2,find(",",a2)-1)

  4. Hi I'm trying to remove quotation marks with the substitute formula but i am getting stuck. Is there a formula to get the following result.

    Text Result Needed
    b" Note Note

    Since excel is not accepting quotation marks in the formula, im getting really confused. thanks for the help.

  5. I have a formatted number say 1523.15 to 1 523.15
    I can't add with it how do I take it out?
    The space between the 1 and 5 is not a " ", so substitute(1 523.15," ", "") did not work. BTW the formatting was done in VB6 by format(1523.15,"###,###.##")

    • Hello Hans,

      Please try the following formula:

      =VALUE(A1)

      Where cell A1 contains the formatted number - 1 523.15.

      Hope it will help you.

  6. Hello,

    I have data in columns as below

    1 xyz abc t.u sfd
    2 a.b mnn jjj i.j
    3 jjj i.o cdf o.p

    I need to replace cell which does not contain '.' with 'NA'. as below..

    1 NA NA t.u NA
    2 a.b NA NA i.j
    3 NA i.o NA o.p

    Finally I need to arrive at something like below.
    1 t.u
    2 a.b i.j
    3 i.o o.p

    Help me on this pls.

  7. hi,

    i need this MWCU 677.352-8 need to come MWCU6773528

  8. if possible I would like to strip everything and get the Order ID. the problem is : Location of the order id and sometime it begins with A or 1. Cell Example A1: {"Ref":"bad order","OrderId":"ABSER27"} Cell Example A2: {"OrderId":"ABSER27"} Cell Example A3: {"order_id":"12345678","customer_email":"BADORDER@gmail.com"}

    Desired result: B1 = ABSER27 Desired result: B3 = ABSER27 Desired result: B3 = 12345678

  9. I have a column of scanned numbers in column "A". some of these numbers are repeated several times down column "A". I need these numbers in column "A" to be changed to a predetermined part number in column "B" every time they appear eg:
    A B
    Code Part Number
    45627 7yh7895
    214789 4hj21345
    496581 65753p
    45627 7yh7895
    45627 7yh7895
    214789 4hj21345
    45627 7yh7895

    Can this be done using substituent& replace function in excel.
    Thanks for your assistance

  10. My previous question didn't read as I would've liked

    The first 3 things are in column F: then the substitution (where 23 subs in for 14) is in column E. With the subsequent chance with 23 subbing in for 14 (I have that backward in my pervious question. With the new list (with 23 in and 14 out) now in the F column.

    So column E would read:
    E2: whatever
    E3: whatever
    E4: whatever
    E5: sub in 23
    E6 sub out 14

    column F would read:
    F2: (1, 2, 14, 32, 55) (this is the starting list which is provided)
    F3: either the list again or blank, it doesn't matter which
    F4: same as F3
    F5: (1, 2, 23, 32, 55) this could happen in F5 or F6 it wouldn't matter, but I would want excel to recognize the 23 in and 14 out and make the change in column F.

    Is that possible? If so what is the most efficient way to do it?

    Thanks for your help.

    Kevin

  11. Hello, I am trying to find a way to have a column where excel finds a substitution and replaces one person with another. So, it would look like this.

    Column E: Column F:
    IN (1, 2, 14, 32, 55) (the in isn't important, just the numbers)
    IN (1, 2, 14, 32, 55) (or blank, whichever,)
    " " "
    sub in 14 IN (1, 2, 23, 32, 55)
    sub out 23

    And so on and so forth. Is this possible?

    Thanks.

  12. thanks Svetlana

  13. can you please change the following format into date :
    17.12.26(W52)

  14. Hi :)

    I am loving this web site. I have learnt more in the past few weeks than what I've learnt in years!

    I am however trying to find out a way to do the following.

    I have a column with a series of numbers - 1 means Paid, 2 means Unpaid 3 means Cancelled etc - Now what I want to do is replace the numbers with its text value. I've been tryng a couple of formulas but they not working quite like I want it too - example if its 1 then change it to paid, if its 2 then change to unpaid and so forth.

    How can I do this?

  15. Hello! This is similar to what I need. I need to replace (or substitute) a character (and just the first of this character) in ALL of our products.

    We have product Spuhr-SP-3016 and AI-0497. I need to replace just the first "-" in the Spuhr item with a ":" and the "-" in the AI item which is the only "-" with a ":".

    This will also need to be done for every product in the A column, not just a single cell. Every example I find is for one cell. Is this possible with a Replace or Substitute formula for all of column A?

    • Hello,

      If I understand you correctly, the names of the products are placed in different cells of column A. Let's assume that 'Spuhr-SP-3016' is in A1 and 'AI-0497' is in A2, and you want the new values to appear in column B. Enter the next formula into B1:
      =SUBSTITUTE(A1,"-",":",1)

      and copy it down the column B.

      But if you want to replace the characters right in the cells where the names are situated, you need to use Find-Replace. But in this case, you must be careful not to change the second '-'.

  16. But display should be as in E12 & E18. In counting total, the amount in E12 & E18 should be substituted with F12 & F18. I think it is clear now. Pls help me.

  17. Hi,
    I have problem with adding, for example the column E from 10 to 25 currency numbers. total added in E 26. Also currency numbers in F12 and F18, I want to substitute E12 and E18 to F12 & F18, but only while counting to the total. Please help me.

  18. I want to make a value as "0" for the number falls between -10 to +10. Which formula can i use.
    Please suggest

  19. Im trying to work out how to replace a formula with another formula.
    Cant just drop it down as it will mess up my subtotals.
    Any suggestions?

    • Hi Sal,
      there's slightly different Excel option you can use: Find and Replace. The very firs part of the article covers what you need, look for 'Excel Find - additional options' subtopic, and you will see how to find and replace various data in your formulas. Hope it helps!

  20. I want add dash in a coloumn which consists of clients name ..

    clients name is as follows
    jidf
    Baysee
    carparl

    it should looks like

    JI_DF
    BAYS_EE
    CARPA_RL

    after last two words i need to add an dash.
    Plese help me to sort it out,

    Regards
    Sunil

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)