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

  1. I have a spreadsheet that I'm trying to build that allows a cell to have a list to choose from. Once the number has been chosen, it replaces verbiage in another cell. for instance, it the number the tech selects in the title cell is 65337-1, it has a configuration within the cells that changes if they choose 65337-2. Not sure if this possible or not.

    Thanks

  2. I want to find and replace a word but the color should not change.
    For example,
    we have a word "confirm" which is in green color and i should replace this word with the word "request" with the same color. What is the formula?

  3. I am looking for a formula which can work in combination with the IFERROR function. On my spreadsheet, I am getting a #DIV/0 error since one of the cells required for a calculation is containing a zero; so I would like to use the IFERROR function to return a zero in a case of an error; moreover, input another 'manual' formula e.g. cells D3/(N3-O3) to come into action when the cells currently containing zero are replaced with >0 numbers. Is there a way I can do this?

  4. I want the difference between old and new numbers in column c,
    in which data in column A gets refreshed every 5mins. column B should be replaced by the old value of column A and difference in column c.
    eg-
    Time A B C
    1.00 1 0 1
    1.05 4 1 3
    1.10 6 4 2
    1.15 2 6 -4
    1.20 -5 2 -7
    1.25 10 -5 15

  5. I have an database whereby I wish to replace partial text in a string using a vlookup in a table on a separate worksheet as follows:

    Worksheet1:
    A1: this is a test replacement for MPv3 filename
    A2: filex32 exists

    Worksheet2: This is where I store a table for looking up exact matches against the field text in Worksheet 1
    Table Replacement
    ====== ================
    MPv3 MP3
    MPv4 MP4
    b@listic balistic
    / {leave it blank}
    x32 x86

    Ideally a substitute function in Column B fields 1 & 2 would result in the fields as per below:
    A1: this is a test replacement for MPv3 filename
    A2: filex32 exists
    B1: this is a test replacement for MP3 filename
    B2: filex86 exists

    I assume the calculation to substitute the text in A1 would use a vlookup and substitute function however I can't seem to get the formula correct.

    Thanks in advance for your assistance.

    Regards,
    David

  6. Very useful. Thank you.

  7. Hello:
    I have a string such as this: BIS-2018-0006-99049&attachment
    Need each row below to add +1 to the 990449 in a batch of say 50
    SO next entry BIS-2018-99050&attachment, followed by BIS-2018-99051&attachment,
    and so on.
    Thank you!
    James

  8. Hi there!
    Below is my question, I often get confused with formulae so pls help.
    I am preparing financials and in YTD November sheet in one of the cells the formula is
    ='Cons Jul'!B37+'Cons Aug'!B37+'Cons sep'!B37+'Cons Oct'!B37+'Cons Nov'!B37
    Now I need to copy this sheet and prepare it till YTD December so the formula would be
    ='Cons Jul'!B37+'Cons Aug'!B37+'Cons sep'!B37+'Cons Oct'!B37+'Cons Nov'!B37'Cons Dec'!B37
    There are a number of cells to be changed manually but can I do it using some other short cut?
    Thanks!

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

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

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

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

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

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

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

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

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

  15. Thanks alot bro!!

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

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

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

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

  18. Very helpful tip. Thank you.

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

    1. kindly try below formula for this:-

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

      Please keep your text in cell a3.

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

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

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

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

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

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

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

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

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

  28. Hi How do i do 1234 to 1324 using formula?

    1. =replace(select word,1,4,"1324")

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

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

      Hope this help.

  30. 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 :(

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

    Regards,
    Ian

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

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

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

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

    AT SINGLE TIME HOW TO REPLACE PLEASE SUGGEST

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

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

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

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

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

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

    1. Hello,

      Please try the following formula:

      =SUBSTITUTE(A1,"b"&CHAR(34),"")

      Hope it will help you.

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

    1. Hello Hans,

      Please try the following formula:

      =VALUE(A1)

      Where cell A1 contains the formatted number - 1 523.15.

      Hope it will help you.

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

  39. hi,

    i need this MWCU 677.352-8 need to come MWCU6773528

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

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

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

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

  44. thanks Svetlana

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

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

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

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

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

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

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

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