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 2. Total comments: 301

  1. Please Help!
    Lets say A1 = 8x12 as a text. I need to replace "x" with " x " . Respectively I need to add space between x and number on each side.
    So I would write SUBSTITUTE(A1,"x"," x "), but it works only on some occasions while it does not work on others the only difference between occasions is the numbers on either side of x (like 40x70 or 100x30 and so on).

    Can anyone of you please explain how to fix this so it works on every instance or suggest an alternative that works indefinitely?

    Thanks in advance!

  2. Hi , May I get help regarding issue mentioned. Thanks a lot.
    If i update C2 with text, A2 will be updated
    If i delete text in C2, key in D2, A2 will be updated with text from D2
    If i delete text in C2/D2, key in E2, A2 will be updated with text from E2
    My concern here is , I want to reflect A2 as latest one whenever there is input in C/D or E.
    My case here, input to C/D or E will be one after another, where A2 to reflect with latest info from C/D or E.
    Thanks

    My current formula used in A2 is =IF(C3="C","Cyellow",(IF(D3="D","Dyellow",(IF(E3="E","Eyellow","")))))

  3. I just want to say this was incredibly useful!, that nested replace is a game changer, thank you so much.

  4. Hi,
    I got following string in one cell:
    _1x111xAx0_22x222xAAx00_333x333xAAAx000_4x444xAAAAx0000_55x555xAAAAAx00000_(etc.)
    As you can see there are segments divided by _
    I need a formula that will change every first x in each segment to /
    Result should be:
    _1/111xAx0_22/222xAAx00_333/333xAAAx000_4/444xAAAAx0000_55/555xAAAAAx00000_(etc.)
    Any ideas?
    Thanks!

    1. @Sreco

      Actually, a better way...(tested, it works, and copy/paste scale-able):

      Sheet1!A1=your string

      Sheet2!A1=1
      Sheet2!B1=FIND("x",Sheet1!$A1,FIND("_",Sheet1!$A1,Sheet2!A1))
      Sheet2!C1=FIND("x",Sheet1!$A1,FIND("_",Sheet1!$A1,Sheet2!B1))
      etc...

      Sheet3!A1=Sheet1!$A1
      Sheet3!B1=REPLACE(Sheet3!A1,FIND("x",Sheet3!A1,Sheet2!B1),1,"/")
      Sheet3!C1=REPLACE(Sheet3!B1,FIND("x",Sheet3!B1,Sheet2!C1),1,"/")
      etc...

    2. @Sreco

      hmmm...not a formula, but you could use:
      Data > Text to Columns > Delimited > Other (_)
      to turn that one column of cells into a multiple column array,
      then use the substitution formula to swap the first instance of x in each cell
      then use the concat formula to rejoin them, or just use
      =Sheet!A1&"_"&Sheet!B1&"_"&etc...
      maybe with a switch function that counts the number of rows and modifies the above formula
      if there aren't a consistent number of "_" in each string...

      pretty manual, but less manual than doing it by hand.
      I'm sure there's a better option, especially if using VBA.

  5. when using the substitute formula, how can get the numbers to display correctly. this is the formula i am using:
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($D$2:$D$5000,"1","item1"),"3","item3"),"13","item13")

    what it should do is look for instances of a number in column D, and put a word in place of the number.
    so if d:3 has a "1" in it the return is "item1". the problem i have is that when "13" is the number the formula returns both 1, and 3, values: "item1item3".

    how can i get this formula to accurately display "1", "3" and "13"?

    1. Hello!
      The formula below will do the trick for you:

      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($D$2:$D$5000,"1","item1"),"3","item3"), "item1item3","item13")

  6. Hi,

    Please help, I need to replace text from " HD " to " A " for example if the range from A2:AD2 the sum of text of " HD " is >2 so here I need to replace the " HD " under in same range A2:AD2 except over and above 2 " HD" cases.

  7. How to delete/hide specific words in a cell and just show what I want to show?

    Example:

    On Cell G2, this is the text: "Here is my task for today, hoping to finish it asap."

    I want to just show this word in another cell: "today"

  8. Hi I'm hoping you can help me ...

    I have the following Exchange Powershell command and I would like the values in quotes to be equal to details stored in another cell on the same row.

    Set-DistributionGroup -Identity "value1" -Name "value2" -DisplayName "value3" -IgnoreNamingPolicy

    This Powershell command would be located in Cell F2. I would like the result to replace value1 with contents of Cell A2 and replace value2 & value3 with contents of Cell B2. I am then wanting to copy the working formula for around 700 rows each time replacing the "value?" entries with Cell A and Cell B of the corresponding row e.g. copying formula to F3 would populate with Cells A3 & B3 and so forth.

    Cheers,
    Duncan

      1. Hi Alexander,

        Thanks so much for replying and pointing me in the right direction ... took me only 5 minutes o get it working I just needed an "=" at the front of the example you provided ... see below.

        ="Set-DistributionGroup -Identity " & A3 & " -Name " & B3 & " -DisplayName " & B3 & " -IgnoreNamingPolicy"

  9. Trying to see if I can replace a value (number) from on cell with 2 different letters depending on the value. So if returned value from formula is 1 in A3 then BL is displayed in A4 (the new cell for this new formula). If value is 2 then OR is displayed. 3 then GR is displayed and so on up to 12? I know that you can do single letters for a given number but need 2 letters to differentiate in further letters. Thank you in advance.

    1. Hi!
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you use CHOOSE function.

      =CHOOSE(A3,"BL","OR","GR",..........)

      I hope it’ll be helpful.

  10. how to replace the text in the same row but in multiple columns in a workbook??
    As in my workbook, one column is Code Second is Catagory which is the same I need to replace the category column with another code no. i can replace in 30 sheets it's difficult

    1. Hi!
      An Excel formula can only change the value of the cell in which it is written. For your task, you need to write VBA code

  11. 1 year 9 months = 1.9
    can someone help with the above results?

  12. =REPLACE(E2, 1, 79,)
    =SUBSTITUTE(F2,CHAR(9),"")
    How can i combine these formulas?

    1. Hi!
      I am not sure I fully understand what you mean. Your formulas refer to different cells. You can combine them using the IF function. But you didn't specify the condition for this. Your question is not entirely clear, please specify.

  13. exmple
    row c5 2020 41566565
    if found in row c5 such cell change in r5521
    its cell complete change

    how can change data such type

  14. Hi, very nice thing you got here going on.
    I've encountered a little problem with the substitute function. Assuming this is the formula I use in my reference cell S2:
    =SUM(IF(AY2=AZ2;1;0)+IF(AY3=AZ3;1;0)
    And then I use in another cell:
    =SUBSTITUTE(SUBSTITUTE(S2;"AY";"BG"),"AZ","BH")
    to keep the formula the same, but to have every AY substituted by BG, and every AZ by BH. However, I can't get it to work. How come?
    Thank you in advance.

    BTW I did notice that in your examples, in the examples, ', ' (a comma) is used, whereas I use ';' (a point comma). But the point comma is automatically used by the excel I use itself, so that can't be the issue I guess.

  15. I have a conversion table. I would like Excel to automatically change the numbers when I type them in.
    For example;
    if I type in 176123456789, I'd like it to change the numbers to 176xxxxx6789
    A part of the conversion table looks like this:
    176xxxxx6789 after i type the number inside

    and it continues. is this a possibility?

    1. If your number length is fixed then you can use below formula.
      =SUBSTITUTE(A2,MID(A2,4,5),"xxxx")

  16. I have a conversion table. I would like Excel to automatically change the numbers when I type them in.
    For example;
    if I type in 10.25, I'd like it to change the numbers after the decimal point to .4... final number should look like 10.4
    A part of the conversion table looks like this:
    .10-.15=.2 (.10 THROUGH .15 is .2)
    .16-.21=.3
    .22-.27=.4
    and it continues. is this a possibility?
    Thank you for your consideration.

    1. Hi!
      You cannot change the number that you wrote in a cell using an Excel function. You need to use VBA macro. In the adjacent cell, you can use the IF function to get the number according to your conditions.
      I hope I answered your question. If you have any other questions, please don’t hesitate to ask.

  17. i want to enter numbers that will be translated in text (pre-defined for each number)
    for example , if i enter "0" in a cell and press enter, it will be translated as "negative"
    1 = positive
    2 = good ,etc

    1. Hi ATTA:
      You can use the following formula:
      =IF(F5=0, "Negative", IF(F5=1, "Positive", IF(F5=2, "Good", "False")))

  18. Hello,

    How i can replace below .

    11.11111.1111 to 11.11111-1111

    the last dot should be dash.

    Best regards

      1. Hi,

        Sir,

        Thank you very much.

        Best regards

        1. Hi Shajith,

          You can also use the following formula:
          =REPLACE(B20, 9, 1, "-")

  19. I used "SUBSTITUTE" function to remove commas from a number but now I can't add the number I got with other numbers since it is now stored as a text I guess. Any solution for this??

    1. Hi!
      If you are talking about a decimal point, then use the rounding functions. If this is not what you want, please explain in more detail.
      Give an example of the source data and the expected result.

  20. For some reason, when I try to use the SUBSTITUTE() function in VBA code, I get an error that says "Sub or Function not defined" when running the macro, with the "Substitute" portion of the code highlighted, as if VBA can't find that function. Am I missing a reference package or something? I thought Substitute() was part of the base Excel install. Why isn't VBA find that function??? Replace() it can find, and everything else I've tried, just not Substitute().

    BTW, if I type it directly into a cell as a formula, it works OK there. Just not in VBA for some reason.

  21. Is there any possibility of adding specific texts to allocated number values please?
    e.g.

    0 = None
    1 = Tea
    2 = Cocoa
    3 = Coffee

    Thank you in advance for any suggestions.

    1. Hi!
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:

      =CHOOSE(B1+1,B1&"None", B1&"Tea", B1&"Cocoa", B1&"Coffee")

      If this is not what you wanted, please describe the problem in more detail.

  22. Is there any possibility of adding specific texts to allocated number values please?
    e.g.
    0 = Not Yet Achieved
    1 = Pass
    2 = Merit
    3 = Distinction

    Thank you in advance for any suggestions.

    1. hi Cam,

      You can use the following formula:
      =IF(F9=0, "Not Yet Achieved", IF(F9=1, "Pass", IF(F9=2, "Merit", IF(F9=3, "Distinction", "False"))))

      where, F9 is the first cell number with the number 0.

  23. how can I change this format to 04-05-2021 AM 2:45:55 to 04-05-2021 2:45:55 AM

    1. Hello!
      If your values are written as date, then apply a custom date format as described in this article.
      If your date is written as text, use the formula

      =REPLACE(LEFT(A1,LEN(A1)-8),SEARCH(" ",A1,1),1, RIGHT(A1,LEN(A1)-13)&" ")

      I hope I answered your question. If something is still unclear, please feel free to ask.

  24. The string is an NCAA basketball tournament game schedule as follows:

    6:30 pm — No. 16 N.C. Central vs. No. 16 Texas Southern (West/Dayton, OH), TruTV

    Where each team name can be one, two or three words as follows:

    4:00 pm — No. 5 Ohio State vs. No. 12 South Dakota State (West/Boise, ID), TNT

    Have used substitute to replace all unnecessary characters so that desired data elements are all separated by a single space...

    6:30 16 N.C. Central 16 Texas Southern West Dayton OH TruTV

    4:00 5 Ohio State 12 South Dakota State West Boise ID TNT

    Each space separated element (other than team name) needs to become its own field in the record. Team name is a separate field. If the pm time could be appropriately valued numerically, that would be a bonus for sure.

    Thank you for any suggestions.

    Yours is an excellent site. I have no problem purchasing a license for your tool set, but I'm not sure if the mechanics are there to execute this very specific string extract problem...

    1. Hi Sam,

      You can use our Split Text tool to split your strings by this mask:
      * — No. * * vs. No. * * (*), *

      The following image shows the settings and outcomes:
      https://cdn.ablebits.com/_img-blog/_comments/comment-640523.png

      Also, you can download a trial version using this link and test it on real data. Perhaps, you will need to additionally remove some unwanted characters by using the Remove tool.

      If you have any further questions or concerns, just let me know :)

  25. Hi,

    its possible to rename needed phraze from Cell1 to needed phraze from Cell2?

    for example -->
    =SUBSTITUTE(A1;"cell1 phraze";("phraze from different cell2"))

    is there option to read information from other cell?

    1. Hi,
      If "Cell1" is cell A1, then this is not possible. If A1 says "cell1 phraze" then where do you want to write the formula that will change this phrase?
      If this is not what you wanted, please describe the problem in more detail.

  26. I need to copy a delimited cell into another cell, but only the values between the delimiters that are less than 30 characters long.

    E.g.

    Reference cell: blue||green||Adding another tag||this tag is toooooooooooooooooooooooo long||this tag is good||short tag

    I want to extract all of the tags that are under 30 characters and put them in the next cell so it would be:
    blue||green||Adding another tag||this tag is good||short tag

    I know how to do this from a column, but need to figure out how to do it from a delimited list and am having no luck.

    1. Hello!
      You need to split the text into cells and then select the ones you want. I recommend paying attention to the tool Split Text. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free. If something is still unclear, please feel free to ask.

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

    Instead of substituting PR with “Project” how would you substitute PR with the content of a cell ?

    Example content of A9 = “Happy”
    Result= PR becomes Happy

    Need to do this with a list of names that will have their own unique URL

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =SUBSTITUTE(A2,"PR", A9)

      If this is not what you wanted, please describe the problem in more detail.

  28. How can I substitute max to AJanMAX

  29. How can i convert " 19:00-20:00-UAE " to " 1900 - 2000" format.

  30. I'm cleaning a large list of addresses with all different formatting. Some were in all Caps (so I cleaned that by using the Proper formula), some spell out Road vs Rd. vs Rd (no period). I'm using Find and Replace, however, I'm running into issues. When trying to find all the addresses using 'Road' and Replace with 'Rd', it will also replace Roadway to Rdway which I do not want. Another example is Finding all the 'Se' (for South East) to Replace as 'SE', excel finds all words that start with Se such as 'Second' and will replace it with 'SEcond'. Is there a way to search for just a two-letter word (ie; Se, Ne, Nw, etc) so I can replace just what I want? Or just look for the word Road and not Roadway? Any advice will be helpful. Thank you

    1. Hello!
      You can search for the desired word in 3 steps. To find an exact match for a word, add a space after the word in the search box. Then repeat your search and add a space before the word. After that, search for the word and use the "Match entire cell contents" option.
      I hope my advice will help you solve your task.

      1. Thank you. Your advice has been very helpful.

  31. Hello,

    I am looking for a formula that will convert seconds into weeks, days, hours, minutes and a way to only show these if the value is not 0. So for example, 13500seconds would convert to 3h 45m.

    Thank you!

  32. Excel has empty commas in one column e.g. ,, OR ,,, OR ,,,, they are not same number but all are empty.

    We want to add text to other columns e.g. "A" "B" "C" etc.

    We want to have a substitute formula that will replace all spaces in column one with text available in other columns.

    So if there are two ,, we want to replace with A,B,C
    if there are three ,,, we want to replace with A, B, C, D
    if there is one then replace with A,B

    how can we do this in excel? Thanks in advance!

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =LEFT("ABCDEFGHIJKLMNOPQRSTUVWXYZ",LEN(A1)+1)

      But if you want to replace commas with letters in the first column, then this is only possible with a VBA macro.

  33. Hi!

    I have column of numbers formatted as follows:

    412.65
    11.987
    6.960.264
    199.246
    0
    90.97
    1194.2
    90.375
    7.8
    1.001.479

    The first period (.) from the right is a decimal point (we use commas), the other points are thousand seperators. The fields with three digits next to the last period are seen by Excel as whole numbers, the others as text. For example 1.001.479 is seen as one million, one thousand seventy nine, and not as 1000,479. 412.65 is seen as text.

    Is there a formule to convert this column into proper numbers, the result should be:

    412,65
    11,987
    6960,264
    199,246
    0
    90,97
    1194,2
    90,375
    7,8
    1001,479

    Basically, the most right period is a comma, all other periods removed?

    Thanks a lot in advance,
    Erik

    1. Hello!
      Please try the following formula:

      =SUBSTITUTE(IFERROR(SUBSTITUTE(A1,".",",",LEN(A1) - LEN(SUBSTITUTE(A1,".",""))),A1),".","")

      Hope this is what you need.

      1. Thanks, Alexander, that does the trick!

  34. I want to replace time format i.e. for example 09:10:32 with word P and if the cell is blank with A , kindly help me with the formula

    1. Hi,
      An Excel formula can change the value only in the cell that it is pasted in. If you need to change the content on the cell that has some value in it, you’ll need to use a VBA macro.

  35. I Want to do a find and replace in formula form. I get Dates sent to me in a specific column " dd/mm/yyyy hh:mm:ss AM/PM". when I past information in to my workbook I want to format that column for it to Automatically change to "ddd hh:mm:ss AM/PM

    Any assistance would be appreciated.

    1. Hello!
      Set your column to "ddd hh:mm:ss AM/PM" format. When you paste values there, use Paste Special - Values. You can use the key combination CTRL + ALT + V then V and ENTER again.
      I hope it’ll be helpful.

  36. Hello Alex,
    How do I change the first zeros in numbers without affecting zeros that are in other positions; for example in the following values: 0Z105, 0Z1025, 0X 11605, 0Y112008; how do I replace only the zeros that come before letters Z,Z,X and Y? Many thanks in advance.

  37. How do I Substitute part of a column for what is in another column. For example, Column E5 says ""Client Name" Branding". I want to substitute the "client name" in E5 for what is in Column A4, which is Zion. So I want it to say "Zion Branding". Column A4 is a pivot table so that I can change the client name as desired and it would change E5 to read the selected client name and then branding.

  38. how to change below text with quantity (Q:100Adam-USA) to (Adam-USA) & it's copy for one column...???

    1. =REPLACE(text ,2,5,"")

    2. Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUBSTITUTE((CONCAT(IF(NOT(ISNUMBER(--MID(SUBSTITUTE(A3,"Q:","",1), ROW($1:$93),1))), MID(SUBSTITUTE(A3,"Q:","",1), ROW($1:$93),1),"")))," ","")

      I hope it’ll be helpful.

  39. Thank you!

  40. How do you substitute the character " ?? Excel returns with invalid, when you try to create formula =SUBSTITUTE(A4,""","") to remove all " in your text.

  41. Hi,
    The substitute formula is picking up part words instead of whole words. How do I over come this?

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result. Please specify what formula you used and what problem or error occurred.

  42. I am trying to concatente & substitute within the same fomula. I have worked out each formula separately, but I cannot put them together into the one formala - can you help?
    =SUBSTITUTE(H6," ","_")
    =CONCATENATE(H7,".jpg")

    I'd be very grateful for your help.
    Kind regards
    Leeanne

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =CONCATENATE(SUBSTITUTE(H6," ","_"),".jpg")

      Hope this is what you need.

  43. Good day,

    Is it possible to have the information in the first tabe replaced with new information on the next line in tab 2.

    For example A3 is equal to 1991 and is extracted from tab (sheet 2) B4. When I add information in tab (sheet 2) B5 i want that to change A3.

    Thank you for your time

    1. Hello!
      You didn't say exactly how you want to change A3 when B5 changes. But the formula might be something like this:

      =IF(Sheet2!B5="",Sheet2!B4,Sheet2!B4+Sheet2!B5)

      I hope it’ll be helpful.

  44. How do I change the item codes of a given list of products. The item/ product descriptions remain unchanged but new item/ product descriptions have been assigned to all the products.

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  45. I have the letters iii that repeat in various cells throughout a workbook. I would like to replace iii with a series of number where I can assign what number goes in for the first iii found and each subsequent iii gets replaced with a "+1" number. For instance, the first iii would be replaced with 300, the second instance of iii would be replaced with 301, the third with 302 and so forth.

  46. How do I replace a certain list with another list? Thanks

  47. Hi,
    I want change the commas and replace as semicolons only outside brackers. But i dont want to change the commas within brackets.

    // Identity information (name, employee ID, date of birth, age, gender) , Home contact information , Businnes Contact (employee name, address) //

    Could you please assist me.
    Thanks
    Deepa

    1. Hello!
      It is very difficult to solve your problem with a formula.
      We have a tool that can solve your task in a couple of clicks: Ablebits Data - Split Text.
      You can split the text into columns. Use brackets () as delimiters. You can then replace the comma with a semicolon in the columns you want. With the Merge Cells tool, you can merge text into one cell again.
      This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
      If you have any (other) questions, don't hesitate to ask.

  48. Net Price
    4,650,000.00 INR
    1,550,000.00 USD
    2,460,000.00 ERUO

    HOW TO SLOW THIS

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  49. Hi, can some one help me please,
    i have a excel that in column A contains 22 digit numbers data,example (123496789123456178912).
    this number represents alot of staff ( location of sales type etc..)
    if the 4th character is "4" this means DE_SALES if "5" means AT_SALES
    if the 5th character is "9" this means PICKEDUP if "0" DELIVERY
    what is the best formula to use

    1. Hello!
      To extract any character or multiple characters from text, use the MID function. Read more here. Then you can use the IF function to check the condition.
      I hope this will help

  50. how to replace
    $23,282 into $23,838

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