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 2. Total comments: 161
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...
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 :)
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?
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.
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.
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.
=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
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.
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
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.
Thank you. Your advice has been very helpful.
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!
Hello!
To convert seconds to time, divide by 86400.
You can learn more about convert time in Excel in this article on our blog.
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!
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.
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
Hello!
Please try the following formula:
=SUBSTITUTE(IFERROR(SUBSTITUTE(A1,".",",",LEN(A1) - LEN(SUBSTITUTE(A1,".",""))),A1),".","")
Hope this is what you need.
Thanks, Alexander, that does the trick!
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
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.
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.
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.
how to change below text with quantity (Q:100Adam-USA) to (Adam-USA) & it's copy for one column...???
=REPLACE(text ,2,5,"")
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.
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.
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.
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
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.
...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
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
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 -
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"
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).
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.
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.
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
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.
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.
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.
=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".
I guess u should use if function rather then subsitute. because this funtion is working as first come first serve basis.
HOW TO REPLACE ALL CELLS
original replace
307(306) 307
304(305) 304
300(303) 303
AT SINGLE TIME HOW TO REPLACE PLEASE SUGGEST
=left(a2,find("(",a2)-1)
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!
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.
if in cell a2=1/04/2018 apply this formula
=left(a2,find(",",a2)-1)
if in cell a2=1/04/2018,05:14 apply this formula
=left(a2,find(",",a2)-1)
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.
Hello,
Please try the following formula:
=SUBSTITUTE(A1,"b"&CHAR(34),"")
Hope it will help you.
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,"###,###.##")
Hello Hans,
Please try the following formula:
=VALUE(A1)
Where cell A1 contains the formatted number - 1 523.15.
Hope it will help you.
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?
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 '-'.
I want to make a value as "0" for the number falls between -10 to +10. Which formula can i use.
Please suggest
Hello, Siddhesh,
assuming that the number is in A1, the formula may look like this:
=IF(AND(A1>-10,A1<10),0,A1)
For more info, here are a couple of links for you - Using IF function in Excel, IF with multiple conditions.
Im trying to work out how to replace a formula with another formula.
Cant just drop it down as it will mess up my subtotals.
Any suggestions?
Hi Sal,
there's slightly different Excel option you can use: Find and Replace. The very firs part of the article covers what you need, look for 'Excel Find - additional options' subtopic, and you will see how to find and replace various data in your formulas. Hope it helps!
Hi i would like to substitute 0 for 5, 1 for 6, 2 for 7 and vice versa.
Which means that if the original value appears as a 0, it would show as a 5.
If the original value appears as a 5, it would show as a 0.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2, "0", "5"), "5", "0"), "1", "6"), "6", "1"), "5", "0")
This is part of my formula, but when the results are out,
0 stays as a 0 but 5 becomes a 0.
1 stays as a 1 but 6 becomes a 1.
How do i make the substitution go both ways?
Oops sorry that was the formula i was doing when experimenting.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2, "0", "5"), "1", "6"), "2", "7")
This was the original formula
hi,,stated example =SUBSTITUTE(F5,"f5","BFP-06") i wanted that if f5 cell will be inputed the result will be BFP-06..could that be possible,,i really needed it to ease my report..
The only problem now is that my entry for f5 is different in the succedding cell below
Hello,I have business task and I really don´t know how to deal with it. I have table of data e.g.
A B C D Result
2021 5044 NEMR 5038 ?????
and I need to put into cell result: find value of cell D (5038) in column B and replace it with data from column A on the same line. Data in column B are unique and in column A too.
I tried replace, find but probably there will need to be some multi task.
Thank you
Martin
a b c d Result
1 2 aa 2 1
2 4 aa 3 #N/A
3 6 aa 4 2
4 8 aa 5 #N/A
5 10 aa 6 3
=OFFSET($A$1;MATCH(D2;B:B;0)-1;0)
If you don't wish to have N/A than simple error handling can be added:
=IFERROR(OFFSET($A$1;MATCH(D3;B:B;0)-1;0);"Not found!")
Need a formula to transform the data in A1 to B1
Cell A1 Cell B1
John Doe John.Doe@ABC.com
The user types the data in A1 and B1 is auto populated.
The only thing I've been able to come up with is to use the SUBSTITUTE formula to convert the "John Doe" to "John.Doe".
Thanks in advance for your help!
Hi Randall,
Simply concatenate your SUBSTITUTE formula with a text string, like this:
=SUBSTITUTE(A1, " ", ".")&"@abc.com"
Hi Svetlana,
Awesome! That did the trick. Thank you!
I have a different problem. I work with a DB and the owners don't want to change something that would make my life easy. When I download the member file to Excel, it has all the member profile information I need to create the hard copy directory. After I download the file, I have to erase columns and lines and then sort to create the directory file (printer works with an excel file). This is unnecessary work. Especially since there is a view that has all the exact profile information needed. When I copy that view and paste it into excel the four entryies (name, phone, address and Email each have their own cell... perfect! However there's one major problem. Here's the cell that includes the e mail address for all 400 members:
Send an Email to snrisa8 @ aol.com
Send an Email to suealbert1 @ yahoo.com
Send an Email to ralfin @ hargray.com
Send an Email to shanialp @ aol.com
Is there a way to remove the Send an Email to in each cell and only leave the email address? Application owner will not remove the header that only appears when you cut and paste that view.
Thanks,
Jon
Use Find and Replace
Select the column, then in Find and Replace, enter the words "Send email to" and replace with nothing (leave the Replace line blank.
Then click Replace All"
I need to replace variable lenght account numbers with an x for each digit while displaying the last 4 dights. For example xxxxxxxxx1234, xxx1234
I have the same dilemma - did you find a solution?
Hi!
Here's the formula for this task:
=REPT("X",LEN(A1)-4)&RIGHT(A1,4)
Where A1 is the source string.
Hi madam
How we can replace only #NUM! with 0 and other remain same..
Plz
Thank U
Hello Harjeet,
Most likely #NUM! is the error value returned by your current formula. To check this, select any cell with #NUM! and look at the formula bar. If you see a formula there, enclose it in the IFERROR function, like this:
=IFERROR(your formula, 0)
I want to change a particular column values to the different specific values as desired. like if in A1= 190000 it should change in 800000 in B1, if it is 190090 then it should change 800090, if it is 199120 then it should change 800120 n like wise..
i have lots on numbers in column A, which i want to change in specific values in column B.
Any suggestions..
I have two coloumn one is NARRATION and other is REMARKS.
I want to keep REMARKS coloumn intact if narration is XX..
any suggestions.
Hi,
i want to replace space in my between my text tell me how can i do it
Hello Sunny,
You can use a formula similar to this:
=SUBSTITUTE(A1, " ", "text")
Where "text" is a text string that you want to replaces spaces with. If you want to remove spaces, i.e. replaces spaces with nothing, use this formula:
=SUBSTITUTE(A1, " ", "")
Hi Team, Really valuable information on this page, but i'm hoping you can help me with a slightly different formula?
I need to remove the "/" symbol from a date in a different cell, for example, in cell A1 it reads 14/03/1998, and in cell A2 I need it to remove the "/" so it reads 14031998. I have tried using SUBSTITUTE, however this gives me the answer 35868.
Any help would be greatly appreciated!
Thanks.
Just use a format or this should work:
=text(a1,"DDMMYYYY")
Good information but ran into an issue converting:
FROM: 110000.350001.704898.104
TO: Fund^110000^Organization^350001^Account^Program^104
Used the formula: =(REPLACE(REPLACE(REPLACE(REPLACE(B7,1,0,"Fund^"),7,0,"^Organization^"),14,0,"^Account^"),21,0,"^Program"))
Which Resulted to: Fund^1^Organi^Accoun^Programt^zation^10000.350001.704898.104
Any suggestions?
Thank you.
Bob
use this formula ="fund^"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(T3,".","^C^",3),".","^B^",2),".","^A^",1)
Or you can use this the below. As the nested functions resolve the substitution so you can just use 1 (of the first occurrence)
="fund^"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(T3,".","^C^",1),".","^B^",1),".","^A^",1)