Comments on: 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. Continue reading →

Comments page 9. Total comments: 301

  1. Super helpful. Thanks!

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

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

    1. 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!")

  4. 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!

    1. Hi Randall,

      Simply concatenate your SUBSTITUTE formula with a text string, like this:
      =SUBSTITUTE(A1, " ", ".")&"@abc.com"

      1. Hi Svetlana,

        Awesome! That did the trick. Thank you!

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

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

  6. =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,

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

  8. 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?

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

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

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

    1. I have the same dilemma - did you find a solution?

      1. Hi!

        Here's the formula for this task:
        =REPT("X",LEN(A1)-4)&RIGHT(A1,4)

        Where A1 is the source string.

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

  13. 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?

  14. Hi madam

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

    Plz

    Thank U

    1. 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)

  15. 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")

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

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

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

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

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

      any suggestions.

  19. 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???

  20. 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"?

  21. Hi,

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

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

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

    1. Just use a format or this should work:
      =text(a1,"DDMMYYYY")

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

  24. Wow, that was amazing....

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

    1. use this formula ="fund^"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(T3,".","^C^",3),".","^B^",2),".","^A^",1)

      1. Or you can use this the below. As the nested functions resolve the substitution so you can just use 1 (of the first occurrence)
        ="fund^"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(T3,".","^C^",1),".","^B^",1),".","^A^",1)

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

  27. 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!

    1. 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")

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

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

  29. This is very use full.

    Thanks a lot.

  30. 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!

    1. 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)

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

    Many thanks Cheusheva !

  32. Wao, great team work... very helpful

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 :)