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

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", 2) - Substitutes all occurrences of "1" with "2".

Excel SUBSTITUTE function

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 different characters 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 multiple characters 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

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. For better understanding of the formulas discussed in this tutorial, you are welcome download the Sample Replace & Substitute worksheet. I thank you for reading and hope to see on our blog next week.

You may also be interested in:

119 Responses to "Using REPLACE and SUBSTITUTE functions in Excel - formula examples"

  1. Slamat Ali says:

    Wao, great team work... very helpful

  2. Naser says:

    That is very much effective steps to post like this which can improve our skills in professional level.

    Many thanks Cheusheva !

  3. rcl says:

    Is it possible to use "*" in SUBSTITUTE? I need to delete all texts containing ".com" plus any other text (I have .com .com.ar .com.ru .com.xxy)

    I try this but it doesn´t work:

    =SUBSTITUTE(A1,".com*","")

    Any suggestion? Can I use wildcards with SUBSTITUTE?

    Thanks!

    Thanks!

    • Hello,

      The SUBSTITUTE function in Excel doesn't support wildcard characters. You can either use a different formula or the standard Find and Replace tool:
      - press Ctrl+H
      - enter ".com*" in the "Find what" field, leave the "Replace with" field empty
      - click Replace All.

      If you prefer using a formula, here is one that can do what you need:
      =IFERROR(LEFT(A1,LEN(A1)-FIND(".com",A1)-1),A1)

  4. Hari yadav says:

    This is very use full.

    Thanks a lot.

  5. Frank says:

    The axample of substitue is incorrect, last two formulas are the same. See below. To substitue all occurences, the instance should be left blank.

    =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", 2) - Substitutes all occurrences of "1" with "2".

    • Frank says:

      it should be like:

      =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".

  6. Custox says:

    Let's say I have a column containing capitalized words. Of those words, there are some (but not all) that I want to be uppercase.

    For example,

    BEFORE:
    Abc Apple
    Xyz Qrs Pear Juice
    Sweet Corn
    Cherry
    Mno Grapes

    AFTER:
    ABC Apple
    XYZ QRS Pear Juice
    Sweet Corn
    Cherry
    MNO Grapes

    To make this happen, is it possible to have a couple of additional columns labeled “Old” and “New” that would contain these words I want to uppercase?

    For example,

    Old New
    --- ---
    Abc ABC
    Xyz Qrs XYZ QRS
    Mno MNO

    And then use a combination of either SUBSTITUTE and VLOOKUP or SUBSTITUTE and INDEX-MATCH to uppercase only those words that I have set aside in those two additional columns?

    The solution doesn’t have to solely rest with those aforementioned formulas, just it seems that is where maybe the answer lies.

    Thanks!

    • Rich says:

      There may be an easier way to achieve what you're asking.
      If the old text started in cell A1 for example, you could simply use the formula =UPPER(A1) in the new text column.

      If you also still need to use substitute, you could combine substitute and upper.

      For example if you needed "abc Apple" to become "ABC APPLES"
      =SUBSTITUTE(UPPER(A1),"APPLE", "APPLES")

  7. Ed says:

    How do you set up excel to change a list of text to show a pre-set name when you enter it into a cell? For example:
    you enter: the cell displays:
    abc123 The Brown Corp
    def456 Blue Inc
    ghi789 Green Company

  8. Bob says:

    Good information but ran into an issue converting:

    FROM: 110000.350001.704898.104
    TO: Fund^110000^Organization^350001^Account^Program^104

    Used the formula: =(REPLACE(REPLACE(REPLACE(REPLACE(B7,1,0,"Fund^"),7,0,"^Organization^"),14,0,"^Account^"),21,0,"^Program"))

    Which Resulted to: Fund^1^Organi^Accoun^Programt^zation^10000.350001.704898.104

    Any suggestions?

    Thank you.

    Bob

  9. Bala says:

    Wow, that was amazing....

  10. JAI says:

    Hi,
    i need repeated values like 1,22,333,4444,55555,666666, in row wise...

  11. Ben George says:

    Hi Team, Really valuable information on this page, but i'm hoping you can help me with a slightly different formula?
    I need to remove the "/" symbol from a date in a different cell, for example, in cell A1 it reads 14/03/1998, and in cell A2 I need it to remove the "/" so it reads 14031998. I have tried using SUBSTITUTE, however this gives me the answer 35868.
    Any help would be greatly appreciated!
    Thanks.

  12. sunny says:

    Hi,

    i want to replace space in my between my text tell me how can i do it

    • Hello Sunny,

      You can use a formula similar to this:
      =SUBSTITUTE(A1, " ", "text")

      Where "text" is a text string that you want to replaces spaces with. If you want to remove spaces, i.e. replaces spaces with nothing, use this formula:
      =SUBSTITUTE(A1, " ", "")

  13. Greg says:

    I'm trying to replace someone's name with another in a specific column, so that the name "Mike" replaces "Michael" for example. How/where do I put the substitute rule so that every instance of "Michael" in column B is replaced with the name "Mike"?

  14. Jonny5 says:

    Hi, team,
    I am trying to make a function that will convert shorthand strings of text to full notations based on a table of definitions, much like CUSTOX from March 25, 2016. We notate accounts shorthand in one system due to space limitations, but then are required to spell everything out in another. It is very time consuming to go through and edit the shorthand.
    If we could simply paste in a string of shorthand in excel and it spit out the "translated" spelled-out version, based on a standard list of abbreviations, we could document account history much more efficiently.
    Furthermore, if we could use a data table (again as CUSTOX mentioned) to manage the definitions much more easily, that would be a HUGE, DEFINITE plus.
    So far, I have been successful using a nested substitute string like this:

    substitute(substitute(substitute(A1,"acct","account"),"xfer","transfer"),"pd","paid")

    only I am up to about 15 substitutions (excel 2013) and have more to input. It is getting difficult managing the linear, buried list within one cell. I would much rather use a table like this and have my substitute function reference it:

    abbr definition
    pif paid in full
    cst customer
    bal balance
    od overdue
    pd paid
    chk check
    crd card

    ...and so on....

    Can anyone help me out???

  15. jitesh says:

    I want to change a particular column values to the different specific values as desired. like if in A1= 190000 it should change in 800000 in B1, if it is 190090 then it should change 800090, if it is 199120 then it should change 800120 n like wise..
    i have lots on numbers in column A, which i want to change in specific values in column B.
    Any suggestions..

    • KRISHNA says:

      I have two coloumn one is NARRATION and other is REMARKS.

      I want to keep REMARKS coloumn intact if narration is XX..

      any suggestions.

  16. Manish says:

    1A2DF2GH512J
    How to find how many numeric value are in above text?
    Plz help..

  17. balu says:

    I have date like name & value .. in that i want to know how many times that name occurs in that column..result will be name1 & name2 & name3 ...something like that....pls tell any formula to find out...

  18. Rajan Kumar says:

    If you want to change into below format:-

    18-45-60-12-10 18004500600012010 (result)
    18-45-60-12-11 18004500600012011 (result)
    18-45-60-12-12 18004500600012012 (result)

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"18-","1800"),"45-","4500"),"60-","6000"),"12-","120")

  19. Harjeet Brar says:

    Hi madam

    How we can replace only #NUM! with 0 and other remain same..

    Plz

    Thank U

    • Hello Harjeet,

      Most likely #NUM! is the error value returned by your current formula. To check this, select any cell with #NUM! and look at the formula bar. If you see a formula there, enclose it in the IFERROR function, like this:

      =IFERROR(your formula, 0)

  20. Sridhar AN says:

    Hi
    I have a value in a cell, say 273.75 in A1. Everyday, I download a data into this cell.
    I have another cell with value 275.00 in B1.
    Now, whenever in future, the downloaded value in A1 exceeds 275 say 278, I want B1 to change to 278 automatically. Can we do this by writing a command in the relevant cell?

  21. OK says:

    Hi,

    I wanted to use nested substitute function to replace Heading with descriptions. ex. I had "NEWS" in one cell and I wanted to substituted with "Stories of the day from newspapers around the world".

    It all worked fine at first but at the end I had 36 of these different substitutions and it said "you've entered too many arguments for this function".

    Is there any way I could replace all these 36 headings with descriptions in one go?

    Thanks

  22. Cindy says:

    I need to replace variable lenght account numbers with an x for each digit while displaying the last 4 dights. For example xxxxxxxxx1234, xxx1234

  23. Trish says:

    I want to change certain letters into a number value but when I input the substitute function it said I had too many arguments. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A7,"w","5"),"gs","4"),"c","3","x","2","ru","1")
    What I really need is a formula that changes letter value to a number then I want to add the numbers i.e. wgs=12, or wcx=10
    Can I do this in one formula or do I need to do two separate columns?
    Thank you in advance
    Trish

  24. Philip says:

    Hi!
    I need to replace from various post codes the “*” for some which are highlighted. My problem is that not all post codes have the same length, and when I try to replace, I loose part of the code too.

    For example I have my Column populated with: “MK5 8NG, MK6 2ED*, YO24 1KA, YO1 6DP* etc…”

    As you see not all post codes are the same length and not all post codes are marked with an asterisk (*).

    Thanks for the help,

    Phil

  25. rubab says:

    i need little guidance regarding using excel commands please help
    i have some numeric values in excel sheet and i want to convert the multiple digit values into single digit by recognizing the 4th number of multiple digit value.
    for example the value is 946198763 i want to covert this serial into 2 when there is always 1 at 4th place after 946 .
    this whole value should be replaced by 2 only.
    how can i do this?

  26. anbhav mehra says:

    how to change 32642/319/09-07-08 only change 32642-319/09-07-08 this format all sheet only change firt / to - not change all

  27. RAVEENDRAN says:

    =SUMIFS(D3:D1500,C3:C1500,"=ETL",i3:i1500,"=23/11/2016")

    Above is a command used in an xls sheet by me (in A2). Such lines are used in many other lines also.
    I want to replace 23/11/2016 with A1 (a date field) which I will be changing very often. Kindly advise how to do this.

    With thanks and regards,

  28. Jon P says:

    I have a different problem. I work with a DB and the owners don't want to change something that would make my life easy. When I download the member file to Excel, it has all the member profile information I need to create the hard copy directory. After I download the file, I have to erase columns and lines and then sort to create the directory file (printer works with an excel file). This is unnecessary work. Especially since there is a view that has all the exact profile information needed. When I copy that view and paste it into excel the four entryies (name, phone, address and Email each have their own cell... perfect! However there's one major problem. Here's the cell that includes the e mail address for all 400 members:

    Send an Email to  snrisa8 @ aol.com
    Send an Email to  suealbert1 @ yahoo.com
    Send an Email to  ralfin @ hargray.com
    Send an Email to  shanialp @ aol.com

    Is there a way to remove the Send an Email to in each cell and only leave the email address? Application owner will not remove the header that only appears when you cut and paste that view.

    Thanks,
    Jon

    • Geoff says:

      Use Find and Replace
      Select the column, then in Find and Replace, enter the words "Send email to" and replace with nothing (leave the Replace line blank.
      Then click Replace All"

  29. Randall says:

    Need a formula to transform the data in A1 to B1

    Cell A1 Cell B1
    John Doe John.Doe@ABC.com

    The user types the data in A1 and B1 is auto populated.

    The only thing I've been able to come up with is to use the SUBSTITUTE formula to convert the "John Doe" to "John.Doe".

    Thanks in advance for your help!

  30. Martin says:

    Hello,I have business task and I really don´t know how to deal with it. I have table of data e.g.

    A B C D Result
    2021 5044 NEMR 5038 ?????

    and I need to put into cell result: find value of cell D (5038) in column B and replace it with data from column A on the same line. Data in column B are unique and in column A too.

    I tried replace, find but probably there will need to be some multi task.

    Thank you

    Martin

    • Adam says:

      a b c d Result
      1 2 aa 2 1
      2 4 aa 3 #N/A
      3 6 aa 4 2
      4 8 aa 5 #N/A
      5 10 aa 6 3
      =OFFSET($A$1;MATCH(D2;B:B;0)-1;0)
      If you don't wish to have N/A than simple error handling can be added:
      =IFERROR(OFFSET($A$1;MATCH(D3;B:B;0)-1;0);"Not found!")

  31. Ahsan says:

    in a cell with formula getting result as PGS or GS or S, at the same time I want to replace if cell result PGS with replace with " platinum " similarly GS with "Gold" and S with "Silver"

  32. Simon Reynolds says:

    Super helpful. Thanks!

  33. MARLON says:

    hi,,stated example =SUBSTITUTE(F5,"f5","BFP-06") i wanted that if f5 cell will be inputed the result will be BFP-06..could that be possible,,i really needed it to ease my report..

  34. Joe says:

    What if I wanted to be able remove everything after the 7th character. For example I have something like "UX12345: This is a user story" but the UXXXXXX can be any number. So I want to remove everything after UXxxxxx including the :

  35. Haubert says:

    Hi there.

    An amazing article, so helpful.

    I'm trying to read a number and substitute this for a text string using "the number" to repeat some character.

    Something like that:

    read "5" and substitute it for "*****"
    read "3" and substitute it for "***"

    In this example I've used "*" but I can use any another one.

    Thank you.

  36. Sudip Kumar Tewary says:

    For the Twelve Months Ending Thursday, June 30, 2016

    I have the above text in excel and I need an excel formula to extract ONLY the date June 30, 2016. The formulas I've researched were all for numerical dates (i.e. 06/30/2016). Would anyone know how to do this? Thanks in advance!

  37. Satyam Sharma says:

    Data in Cell A1=MH 99-CA-9875,how to replace all "space" & "-".
    And result should be MH99CA9875.

  38. Anonymous says:

    thanks

  39. Kevin Koo says:

    Hi i would like to substitute 0 for 5, 1 for 6, 2 for 7 and vice versa.

    Which means that if the original value appears as a 0, it would show as a 5.
    If the original value appears as a 5, it would show as a 0.

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2, "0", "5"), "5", "0"), "1", "6"), "6", "1"), "5", "0")

    This is part of my formula, but when the results are out,
    0 stays as a 0 but 5 becomes a 0.
    1 stays as a 1 but 6 becomes a 1.

    How do i make the substitution go both ways?

    • Kevin Koo says:

      Oops sorry that was the formula i was doing when experimenting.
      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2, "0", "5"), "1", "6"), "2", "7")

      This was the original formula

  40. Mike G says:

    I have exported a SharePoint list to Excel and in a cell where I have multiple names I get a ; # and a number. I would like to eliminate them and only have the names with a semicolon separating.
    for example:
    Smith,Sue;#9800;Jones,Edward;#9783;Garcia,Pablo;#12958
    Smith,Sue;Jones,Edward;Garcia,Pablo

  41. Sunil Kumar B.N says:

    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

  42. Sal says:

    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?

  43. Siddhesh says:

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

  44. Susmy Sivan Pillai Vijayalekshmi says:

    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.

  45. Susmy Sivan Pillai Vijayalekshmi says:

    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.

  46. TheCrawford says:

    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 '-'.

  47. Mommaroodles says:

    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?

  48. akn says:

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

  49. rohan says:

    thanks Svetlana

  50. Kevin says:

    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.

  51. Kevin says:

    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

  52. David says:

    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

  53. Pardeep says:

    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

  54. Manikandan says:

    hi,

    i need this MWCU 677.352-8 need to come MWCU6773528

  55. Naveen says:

    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.

  56. Hans says:

    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,"###,###.##")

  57. Saif Ali says:

    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.

  58. Philip says:

    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!

  59. venu says:

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

    AT SINGLE TIME HOW TO REPLACE PLEASE SUGGEST

  60. meowitza says:

    =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".

    • Divya says:

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

  61. Mark Jacobsen says:

    I have four columns of data (A1,B1,C1,D1) - each of which contains either "Y", "N" or is left blank. Any number of cells may be blank or none may be blank. I've used Concatenate to put the string together and, based on the result and a series of IF statement, I render a text string result in another column. For example, "YYYY" = "Good", whereas "NNNN" = "Bad". All well and fine but due to the potential blank cells - the string doesn't properly reflect my options. Essentially I'm expected to derive the result based on a four character string from these Y or N inputs. All I really want to do is consistently replace a blank with a "N", so I can get my four character string and do the final rendering. I want to use only Excel formulas and I think Substitute could be used in combination with Concatenate (and the IF statements) to get me where I need to go.

  62. Ian Smith says:

    Hi,
    does substitute leave non matching substrings within a cell unaltered?

    Regards,
    Ian

  63. Subbarao says:

    Hi Team,

    Can some one please help me with this substitute formula?

    =SUBSTITUTE(SUBSTITUTE(A96,"9(","DECIMAL("),")",",0)")

    this is my input[S9(10) Packed Decimal]and it gives output as SDECIMAL(10,0)

    now i want to update this same formula to get the below result

    [S9(4)V9(2) Packed Decimal]=DECIMAL(5,2) how can i modify this?

    i know this is a silly question but i am new to excel :(

  64. ned says:

    In using the substitute function, when I add an instance number it gives me the #VALUE! error ?? If I omit the instance number it substitutes all instances with no error ??

    Thanks

    • ned says:

      Found the cause of the error: should not check the "Transition formula evaluation" in the Advance Options.

      Hope this help.

  65. sanjiv says:

    Hi How do i do 1234 to 1324 using formula?

  66. AY says:

    For instance, I want to change list of numbers that contains first digits of 081 to 234 but some of the digits also have their last numbers as 081. How do I go about it?. Example : Wish to change the first (081) 08130552081 and not the last to 234

  67. RANYA says:

    {A:CM06ICIC0SF0001RBIP0NEFTSC1220ALJOEUREWKJLESUKOWEUJKD00NRIDF;DLJFJ20171128 1636}
    In this, need to replace Second character A not entire A letter

  68. Erin says:

    Hi team,
    I am trying to find a way to convert the following dataset to numbers.
    eg.
    apple banana orange
    orange apple banana
    banana orange apple

    Where,
    apple = 1
    banana = 2
    orange = 3

    So, I want to end up with
    1 2 3
    3 1 2
    2 3 1

    Any ideas other than using the Replace All button?

    • Hi Erin,

      If you want to display the number equivalents of your text values in the same cells, I'm afraid you will need to use a special macro to achieve this result or keep on using the standard Excel Find and Replace feature.

      However, if we suppose that your table with the text values is in A1:C3, and the table with the number equivalents is in A5:B7, then you can try to enter the following formula, for example, in cell E1:

      =INDEX($A$5:$B$7,MATCH(A1,$A$5:$A$7,0),2)

      After that copy this formula to the adjacent columns and rows. And then you can select the table with the numbers you've got, press Ctrl + C to copy the selection into the clipboard, select your original dataset and use the Paste - Paste Values option to replace the text with the numbers in the original table.

      If your dataset is quite large though, using a macro will be the best option for you. You can search for it in VBA sections on mrexcel.com or excelforum.com.

  69. Sumit says:

    Hello sir
    My sentence is
    Vishal is a good good good boy
    And i want change 2nd "good" into "bad" with subsitute formula can u suggest corret way

  70. Tine says:

    Hi,

    Does anyone have a consistent way to change/replace the following

    Bouvier/Antoine --> Bouvier/Ant
    Paitard Xavier --> Paitard/Xav
    Di Folco/Marc --> Difolco/Mar
    De La Reveliere/Patr --> Delareveliere/Pat
    Kinzelin/Marie Helen --> Kinzelin/Mar
    Ray Jean Pierre --> Ray/Jea

    The result should be lastname/3-letters of first name ...

    Thanks for your help!
    Regards
    Tine

  71. Doug says:

    Tine:
    Because the data is not structured consistently it would not be possible to do what you want. In other words, you need to work the data so that there is a "/" in every place you want a break. I put a "/" at the places in the data to achieve what you wanted.
    First, I put the cleaned and formatted data in G30 and entered this formula into J30.
    Second, I entered this into K30 =LEFT(G30,FIND("/",G30)-1)
    Third, I entered this into L30 =LEFT(J30,3)
    Fourth, I entered this into M30 =CONCATENATE(K30,"/",L30)
    Because of what you want it will take several steps to get you there, but you'll get there.

  72. MADHU says:

    How to swap alternate characters of the contents of a cell in Excel. Example - A cell containing string 123456 should be converted into 214365 and the result placed in another cell.

  73. Vangie Canete says:

    Create Excel rule for automatic text / number replacement

    I want this to be a rule, so I can just type / enter the team number in the next time and it will automatically replace it with the name of the team.

  74. Kenneth Schuman says:

    I have a small problem..
    I have cells that contain several of the same character '_'(quantity varies) and the text 'Category', I wish to remove all the occurrences of '_' along with each 'Category'

    Cells contain a1 abc_abcc_defg_category
    a2 de_a_category
    a3 ghi_cc_126_category

    I wish cells in b1 abc abcc defg
    b2 de a
    b3 ghi cc 126
    I've tried several combinations of substitute, replace, match but with no luck. HELP

    • Peshiya_Boy says:

      kindly try below formula for this:-

      SUBSTITUTE(SUBSTITUTE(A3,"_"," "),"category"," ")

      Please keep your text in cell a3.

  75. Elias Tsolis says:

    Very helpful tip. Thank you.

  76. Amar Deep says:

    Hi,
    7/8/123

    88/45/21105648/16455641

    456487/6459/54654559/634965/464546/2616

    .1/1/1

    how do I replace every second /from the above content by using furmulla.
    please suggest.

    • Doug says:

      Amar:
      I think this is what you're looking for.
      Where the data is in A3 and you want to substitute nothing for the second occurrence of the forward slash try this:
      =SUBSTITUTE(A3,"/","",2)

      Doug

  77. Dinesh says:

    Heyy, I have to create an equation for if the numbers used is more than 5000, then subtract 5000 from it. then from that derived number,i need to find 8% of it. Of course i tried =IF(B3>=5000,B3-5000*8%). this gets me a higher answer than if i were to do it with a calculator. Can you please help asap please??

    • Doug says:

      Dinesh:
      Try this:
      =IF(B3>=5000,(B3-5000)*.08,"Value in B3 is Less Than 5000.")
      It reads, If B3 is greater than or equal to 5000 then subtract 5000 from the value in B3 and then multiply that remainder times .08 otherwise display a message letting the user know that B3 is not greater than or equal to 5000.

  78. Dinesh says:

    Thanks alot bro!!

  79. Mike says:

    I have an address problem. In these examples:
    P.O. Box 851
    23 Green St. LOT 214 A
    414 Aptitude Way
    616 Camelot Dr.
    5 Cook St. APT F 5
    817 South St. LOT 52 B
    44 Quarry Rd. BOX 24 C

    My vendor's software drops the last letter or number after "APT", "LOT", or "BOX" because of the last space. If I give them LOT 52B or BOX 24C it prints correctly. The abbreviations APT, LOT, and BOX will always be capitalized. How do I cut the last space in these strings?

  80. ryan jacob says:

    if i have two columns A and B with different values in n rows
    and i want to replace the column A values with column B values - if the value in column A is less than value in column B
    What function should i write

    A B
    4 8
    7 2
    n rows
    etc
    want the 7 to replaced by 2 and so on in other rows till n row

    what function should i write

    thanks and best wishes
    +ryan

    • Doug says:

      Ryan:
      Your request is unclear. You write, "I want to replace the column A values with column B values - if the value in column A is less than value in column B." Yet your example is the value in column A is greater than the value in column B.
      I'll go with the example. Where the data begins in A2:B2
      the formula looks like: =IF(A2>B2,B2,"T")
      Enter this in C2 and copy it down the column.
      You didn't specify what happens if A2<B2 so I just wrote a "T". You may want to put in something different.

  81. Debbie says:

    Hi
    My formula takes a letter designation for the location of the file and replaces with the actual server address. My issue becomes some laptops will actually add the server address instead, I would need to remove it because a VBA adds the static server address. How can I add this possibility with a logic statement to look and remove if present?

  82. Tim Durham says:

    Great info! I need to convert the first example to second example.

    34 59 48.29195(N)
    34° 59' 48.29195" N

    (so adding the characters in position 3, 6, 15 then replacing first "(" with a space and deleting second ")"

    I am not an excel guru and I tried using the replace and substitute command but just could not figure it out :( Any help will be appreciated.

    • Dinesh says:

      Im not an excel guru neither but i think changing the format of the number from general to something else that may solve this will be good. Perhaps creating a custom format might help hopefully.

  83. Thomas Stange says:

    Hi,

    I have imported information form the internet into excel and it keep updating as intended. The problem for me is that i can use whats being imported to multiply with. eks : 8.214605. This will only work if its a comma instead.
    So everytime it does a new update, the number change and . comes back.

    Any way of making a 2nd cell that changes out the . for a , ?

  84. Deniz says:

    Hello Everyone,
    I understood the logic of SUBSTITUTE, but what if I want to replace more than one digit ?
    For example,
    DENIZ_13579-ISTANBUL
    When I formulate SUBSTITUTE(A2;"1";"") it takes 1 out, that's easy.
    My question is, how can I take all the digits out ?
    and then in another cell only show those digits 13579 ?
    I will appreciate your help
    Cheers

    • Doug says:

      Hello, Deniz:
      There are several different methods for extracting numbers from a text string. Here's one.
      Enter this formula in a blank cell and then with the cursor somewhere in the formula in the formula bar simultaneously press CTRL-Shift-Enter. It's an array formula so it needs the curly brackets around the entire formula which must be entered by simultaneously pressing CTRL-Shift-Enter.
      Replace each instance of C30 with the address of the cell where your text string is stored.
      =MID(SUMPRODUCT(--MID("01"&C30,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&C30,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)
      It has the following caveats:
      - The input string in cell C30 must be shorter than 300 characters
      - There must be at most 14 digits in the input string. (Any following digits will be shown as zeroes.)
      -This formula extracts the numbers as text into the cell in which it is entered. If you need the numbers as numbers then use the VALUE function in another cell that references the cell the numbers displayed as text are in.
      It will also handle the following two cases correctly:
      - If there is a "0" as the first digit of the original string it will be shown correctly in the output
      - An input without any digits at all will display an empty string as output (rather than 0).

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
40
Ultimate Suite 2018.5 for Excel
40
Christmas sale
Best Price of the Year. Dec. 18 – 28