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 5. Total comments: 301
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 , ?
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?
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
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.
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?
Thanks alot bro!!
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??
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.
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.
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
Very helpful tip. Thank you.
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
kindly try below formula for this:-
SUBSTITUTE(SUBSTITUTE(A3,"_"," "),"category"," ")
Please keep your text in cell a3.
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.
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.
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.
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
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
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?
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.
{A:CM06ICIC0SF0001RBIP0NEFTSC1220ALJOEUREWKJLESUKOWEUJKD00NRIDF;DLJFJ20171128 1636}
In this, need to replace Second character A not entire A letter
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
Hi How do i do 1234 to 1324 using formula?
=replace(select word,1,4,"1324")
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
Found the cause of the error: should not check the "Transition formula evaluation" in the Advance Options.
Hope this help.
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 :(