by 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.
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")
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")
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").
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:
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")
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:
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:
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,"/"))
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, ":")
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:
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?
Another practical application of the REPLACE function is to capitalize the first letter in a cell. Whenever you deal with a list of names, products, and the like, you can use the above-linked formula to change the first letter to UPPERCASE.
Tip. If you want to make the replacements in the original data, an easier way would be using the Excel FIND and REPLACE dialog.
Excel SUBSTITUTE function
The SUBSTITUTE function in Excel replaces one or more instances of a given character or text string with a specified character(s).
The syntax of the Excel SUBSTITUTE function is as follows:
The first three arguments are required and the last one is optional.
- Text - the original text in which you want to substitute characters. Can be supplied as a test string, cell reference, or a result of another formula.
- Old_text - the character(s) you want to replace.
- New_text - the new character(s) to replace old_text with.
- Instance_num - the occurrence of old_text you want to replace. If omitted, every occurrence of the old text will be changed to the new text.
For example, all of the below formulas substitute "1" with "2" in cell A2, but return different results depending on which number you supply in the last argument:
=SUBSTITUTE(A2, "1", "2", 1) - Substitutes the first occurrence of "1" with "2".
=SUBSTITUTE(A2, "1", "2", 2) - Substitutes the second occurrence of "1" with "2".
=SUBSTITUTE(A2, "1", "2") - Substitutes all occurrences of "1" with "2".
In practice, the SUBSTITUTE function is also used for removing unwanted characters from cells. For real-life examples, please see:
Note. The SUBSTITUTE function in Excel is case-sensitive. For example, the following formula replaces all instances of the uppercase "X" with "Y" in cell A2, but it won't replace any instances of the lowercase "x".
Substitute multiple values with a single formula (nested SUBSTITUTE)
As is the case with the Excel REPLACE function, you can nest several SUBSTITUTE functions within a single formula to do several substitutions at a time, i.e. substitute several characters or substrings with a single formula.
Supposing you have a text string like "PR1, ML1, T1" in cell A2, where "PR" stands for "Project, "ML" stands for "Milestone" and "T" means "Task". What you want is to replace the three codes with full names. To achieve this, you can write 3 different SUBSTITUTE formulas:
=SUBSTITUTE(A2,"PR", "Project ")
=SUBSTITUTE(A2, "ML", "Milestone ")
=SUBSTITUTE(A2, "T", "Task ")
And then nest them into each other:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"PR","Project "),"ML","Milestone "),"T","Task ")
Notice that we've added a space at the end of each new_text argument for better readability.
To learn other ways to replace multiple values at a time, please see How to do mass find and replace in Excel.
Excel REPLACE vs. Excel SUBSTITUTE
The Excel REPLACE and SUBSTITUTE functions are very similar to each other in that both are designed to swap text strings. The differences between the two functions are as follows:
- SUBSTITUTE replaces one or more instances of a given character or a text string. So, if you know the text to be replaced, use the Excel SUBSTITUTE function.
- REPLACE changes characters in a specified position of a text string. So, if you know the position of the character(s) to be replaced, use the Excel REPLACE function.
- The SUBSTITUTE function in Excel allows adding an optional parameter (instance_num) that specifies which occurrence of old_text should be changed to new_text.
This is how you use the SUBSTITUTE and REPLACE functions in Excel. Hopefully, these examples will prove useful in solving your tasks. I thank you for reading and hope to see on our blog next week!
Download practice workbook
REPLACE and SUBSTITUTE formula examples (.xlsx file)
301 comments
Hi, I have want to convert all cases to PROPER except UPPER Case in Single cell.
E:g 1,
From
"uniTed Arab EmiratEs (UAE)"
To
"United Arab Emirates (UAE)"
E:g 2,
From
"i love APPLE"
To
"I Love APPLE"
Any Solution to this ???
Hello
Can Anyone Help Me... I Want Exctract Only "The following manufacturers are covered:" To "Segment by Regions" In between Words seperate And Same as "Segment by Type" to "Segment by Application"
"Solvent Based
Water Based
Hot-Melt-Based"
I want this words separte...
Can anyone Tell me Any Formula...
Unknown is made up of two parts: base material and adhesive. Two or more unconnected objects are connected together by bonding.
The adhesive tapes market in the Asia Pacific region is projected to grow at the highest CAGR.during the forecast period.
Asia Pacific is the fastest-growing market for adhesive tapes
At company level, this report focuses on the production capacity, ex-factory price, revenue and market share for each manufacturer covered in this report.
The following manufacturers are covered:
3M
TESA SE
LINTECORATION
Segment by Regions
North America
Europe
China
Japan
Segment by Type
Solvent Based
Water Based
Hot-Melt-Based
Segment by Application
Packaging
Healthcare
Thank You....
How to replace to another sheet cell by putting value to current cell?
Svetlana -- your blog is one of the most helpful and straightforward around! You provide crystal clear explanations and illustrations, of multiple ways to achieve the same thing... while not burying the easiest solution. Thank you SO much for all your wisdom and work!
Zach: =SUBSTITUTE(B2, ",",".")
Sunil: =SUBSTITUTE(B2, "18-043-","")
Del: =SUBSTITUTE(B2, "$100.00"," ")
Mike: Among your options are the 3 below. The first 2 would return the email address or term of your choice . Then you could filter to show only rows that aren't blank, and delete them. The 3rd shows the word "send" if the item isn't in the list already mailed to.
=IF(ISERROR(INDEX($D$2:$D$499,MATCH($B2,$D$2:$D$499,0))),"",INDEX($D$2:$D$499,MATCH($B2,$D$2:$D$499,0)))
=IF(ISERROR(INDEX($D$2:$D$499,MATCH($B4,$D$2:$D$499,0))),"","sent already")
=IF(ISERROR(INDEX($D$2:$D$499,MATCH($B3,$D$2:$D$499,0))),"send","")
Hello, I am trying to find an easy way to remove some characters from an excel string. For example, it reads - 1st(sp)15.255(sp)$100.00(9sp)Name - where sp is a space. I want to remove the dollar amount ($100.00) but keep everything else, including the spacing. Any suggestions? Love your website, I have learned so much from it but this one has me stumped. Replace will remove the dollar amount but also everything else behind it. Any help would be greatly appreciated. Thanks.
I am trying to find multiple values in a single column and replace them all without using find/replace one by one. Ex. I have a list of 150 email addresses that have already received a mail merge. Those 150 email addresses are all located in another column in a different worksheet that has thousands of email addresses. How do I search for all 150 that have been sent and replace them with a blank cell?
I like to replace below
18-043-0364-04-622
to
0364-04-622
I am looking to replace a comma in a string of text. The comma may or may not be present so I need to search for it, then if it is present, its location changes depending on the string. Once the comma is located, I need to replace it with a period. I convert all my excel files into .CSV and that comma is messing up my data.
If anyone can help with this I would greatly appreciate it.
Thank you!
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
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?
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?
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
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
Very useful. Thank you.
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
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!
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
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).
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 , ?
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.
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.
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?