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 on: Using REPLACE and SUBSTITUTE functions in Excel - formula examples
by Svetlana Cheusheva, updated on
Comments page 4. Total comments: 301
Hi,
The substitute formula is picking up part words instead of whole words. How do I over come this?
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.
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
Hello!
If I understand your task correctly, the following formula should work for you:
=CONCATENATE(SUBSTITUTE(H6," ","_"),".jpg")
Hope this is what you need.
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
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.
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.
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.
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.
How do I replace a certain list with another list? Thanks
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
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.
Net Price
4,650,000.00 INR
1,550,000.00 USD
2,460,000.00 ERUO
HOW TO SLOW THIS
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.
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
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
how to replace
$23,282 into $23,838
How to replace
search.auckland.ac/search?allPage=1 to search.auckland.ac.nz/search?allPage=1
search.auckland.ac/search?allPage=1 to search.auckland.ac.nz/search?allPage=2
search.auckland.ac/search?allPage=1 to search.auckland.ac.nz/search?allPage=3
search.auckland.ac/search?allPage=1 to search.auckland.ac.nz/search?allPage=4
till page=74
Hello Selvam!
If the list is in column A and starts with A1, then in B1 use the formula
=LEFT(SUBSTITUTE(A1,".ac",".ac.nz"), SEARCH("=",SUBSTITUTE(A1,".ac",".ac.nz"),1))&ROW()
After that you can copy this formula down along the column.
Hope this is what you need.
Can I change a cell value in a table from another cell
Your reply would be highly appreciated.
Thanks, in advance.
Hello Kalpesh!
The Excel function can’t change values in other cells, it returns a value only in a cell where it is written. You'd better use VBA to solve your task.
Hello,
Can we place our new text in other cell than the one with the formula?
I am trying to convert text into time. I am using the TIMEVALUE function and have gotten 1/2 way there so far. Problem is Excel doesn't recognize my data as time because when it was exported, there is not a space between the time and p or a (Ex: 10:08p and 6:31a). I entered the formula: =TIMEVALUE(SUBSTITUTE(H3,"p"," pm") and it worked. The problem I have is that I don't know how to enter the formula to also change the "a" to " am" when this occurs in the column. Hoping someone can offer me assistance. Thank you!
Hi,
Is it possible to use the substitute function in an array, where there are other symbols or words intercalated? For a translation, as below:
Cell A2 : I want to go home.
Cell B2 : Je veux aller à la maison.
Cell C3 : I [slot] want [slot] to go [slot] home. [slot]
Can I bring my translation somehow in D2 with the slots appearing, not necessarily at the appropriate place, but at least to be within the words?
Thank you so much for your help!
N.
Hello Svetlana,
Very Good and helpful Article on Replace & Substitute function.
My question is: Can we use an Array Set for;
SUBSTITUTE(text, old_text, new_text, [instance_num])
OLD TEXT: 0?1-GR&*88945.@
SUBSTITUTE("0?1-GR&*88945.@", {"?";"-";"&";"*";".";"@"}, {"";"";"";"";"";""})
NEW TEXT: 01GR88945
CAN WE DO Like this to Get Results without using Nested Substitute Function.
Thank you
Hi sir
i need Starting first character in a cell should alphabets or numeric if other Character like special character should replace any formula could Remove
how to replace 1, 2,4 to G+0, G+1,G+3 respectively
I have data coming in where A1 is a network name from A2 until a variable amount of lines the cells have an asterisk. Then a space another network name and then more asterisks for an variable amount of lines, then another network name and it continues for about 10K total lines in the sheet. I am trying to replace the asterisk with the network name proceeding its section without doing it manually. I've tried search/replace, failed at a macro, and a few other things.
thank u very much , your tutorial is very helpfull ...
I am trying to create a data matrix. I want to be able to replace any instance of the number 1 in a row with "=b2" So that any instance of 1 in row b will return whatever value I put in cell b2.
I would to be able to drag this formula down or across as the data set I am working with is roughly 600 cells by 300 cells.
I have been accomplishing the same task by highlighting cell b3 to the end of the data field and doing - FIND 1 and REPLACE with =b2. But I must do this line by line manually
Thanks
KAS
=SUBSTITUTE(text, old_text, new_text)
if: a=!, b=@, c=#,... x=>, y=?, z=~, " "=" "
then: abcdefghijklmnopqrstuvwxyz ... try this out
equals: !@#$%^&*()-=+[]\{}|;:/?~ ... ;}? ;*(| ]:;
RULES:
------
(1) text to substitute is in cell A1
(2) max 64 substitution levels (the formula below only has 27 levels [alphabet + space])
(2) "old_text" cannot also be a "new_text" (ie: if a=z .: z cannot be "old text")
---so if a=z,b=y,...y=b,z=a, then the result is
---abcdefghijklmnopqrstuvwxyz = zyxwvutsrqponnopqrstuvwxyz (and z changes to a then changes back to z) ... (pattern starts to fail after m=n, n=m... and n becomes n)
The formula is:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"a","!"),"b","@"),"c","#"),"d","$"),"e","%"),"f","^"),"g","&"),"h","*"),"i","("),"j",")"),"k","-"),"l","="),"m","+"),"n","["),"o","]"),"p","\"),"q","{"),"r","}"),"s","|"),"t",";"),"u",":"),"v","/"),"w",""),"y","?"),"z","~")," ","_")
...i am trying to do a reverse score formula for a data...how do i replace numbers without having the formula repeat back on itself? so for example, if a participant answers a "1" to a question, i want the answer in the cell to come up as "5"...i want the numbers reversed for 1 through 5 (i.e. i want 1=5, 2=4, 3=3, 4=2, 5=1).
my formula was this:
=substitute(substitute(substitute(substitute(substitute(B5,"1","5"),"2","4"),"3","3"),"4","2"),"5","1")
what i ended up creating was a loop, i assume...because each "1" ended up being a "1" (because it made the initial change to "5", and then further along the formula changed that "5" into a "1"), and each "2" became a "2" (for the same reason). 3 4 and 5 were correct (because those numbers didnt show up again in the first position of the number string inthe formula).
So, how do i write the formula so i dont have a loop for those first two numbers?
thanks!
You can use formula
=Max(A:A)+1-A5
In your case: 6-A5
Excellent article explaining the REPLACE & SUBSTITUTE functions, and I was able to correct some phone numbers using the REPLACE example in the article. But what I need to do now, is replace the individual cells with the corrected formatting (i.e. phone number without formatting to phone number with appropriate formatting). The column that has the original values has some cells with correct data and other cells with the incorrectly formatted phone numbers. Is there a simple way (using a formula?) to replace the new formatted number (in column C) with the incorrectly formatted number (in column B). Not every number in the column is incorrect, only about 100 of a total of about 700 entries. I hope I was clear.
169 #N/A
169 #N/A
169 #N/A
169 Punjab National Bank
169 #N/A
169 #N/A
i want to have punjab national bank in all the other places where it is #N/A. please share logic to write in other column
ctrl+H
find #N/A
Replace Punjab Nation Bank
How do i replace cell with ___AHEJ7224 with ___EJ8899 for example, i just want to replace all text in a column with new text leaving the underscore's.
Sir,
thank you so much i found the ans. in my problem the last example is the ans.
Sir,
possible to substitute a two different word or text? example in cell no A5 i would like to write ether PASS or Fail, and for my substitute cell A8 is YES or NO.
to make it clear i want to write PASS in my old text A5, the result in my substitute cell A8 will be YES, but if i write in my A5 cell is FAIL the result must be in A8 cell is NO.
IS THIS POSSIBLE? thank you so much.
Regards,
JPD
Hi Jophet,
You can use if Function to return value Yes if text A5 is Pass while NO if the cell A5 is Fail e.g =IF(A5="PASS","YES","NO").
Thank you.
How to replace 784.1991.1796026.2 to 784-1991-1796026-2
=SUBSTITUTE(YourCell,".","-")
U can Also Use Find and Replace key.
eg. ctrl+H
FIND . AND REPLACE -
Thanks a lot.....
I've found my solution.. It really helps me much
When I import names from a SharePoint list I get the name, a semicolon, and then a number (example: Smith, John;#1234;#Jones,Mary:#567). This number is probably assigned by SharePoint when you add someone so they range from single digits to 5 digits for our company. I am trying to eliminate the "#" and the numbers. I can replace the "#" with a space using the substitute function but am having problems replacing the numbers due to the fact they are not a fixed number of digits. Also some cells could be a single name while others could have five or six names. Any insights?
=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))
Try this. :) If you haven't already got your solution.
What formula do I use to change 13-05-2019 08:51:51 format into 13/5/2019 08:51:51 format?
Thanks
Hi, if you want do this with formula, try this
=TEXT(H29,"dd/m/aaaa hh:mm:ss")
But you can same do, using format menu, with dd/m/aaaa hh:mm:ss
Note: in some cases, you would need change "aaaa" for "yyyy"
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 , ?