How to use find and replace
Product Description
The formula may give you an error after you replace a letter, text string, or a number in a formula.
This happens when the replace function finds and replaces what you are aiming to replace not only in a formula but also in the function name altering it and making it invalid.
For example:
Look at the spreadsheet below, which summarizes monthly data for exemplary schools. The "Full Year Total" column sums data for twelve months, January through December. The formula itself is listed next to the sum result. For example, the formula to summarize results for School 1 is =sum(B7:M7).
Then, for example, you decided that instead of Full Year Total, you want to calculate only results for the first three months, Quarter 1 Total. In order to do that, you will need to summarize only data from January to March. The formula now should say =sum(B7:D7). Basically, letter M should be changed to letter D in the formula. Instead of changing the formula manually, you can do a replace function. It is especially useful when dealing with large data.
In order to replace a letter, text string, or a number in a formula go to file menu and follow this pass: Edit / Replace and then fill in Find what and Replace with. However, if you simply type Find what: M and Replace with: D your results will give you an error and will look like that:
This happened because replace function replaces letter M not only in formula but also in function name "sum", altering it to "sub" and making it invalid.
Also, look at the title. "Total Number of Days missed by students" now says :"Total NuDber of days Dissed by students".
To avoid error messages when replacing a letter, text string, or a number in a formula, you should include a unique character, that is located directly next to what you are replacing, in your replace function. These unique characters will be : ; " ), etc. For example: instead of replacing M with D, you should have replaced :M with :D. You should type find what: :M and replace with: :D. This way replace function will look only for what you specifically identified and will omit others that you do not want to be part of a replace function.
This happens when the replace function finds and replaces what you are aiming to replace not only in a formula but also in the function name altering it and making it invalid.
For example:
Look at the spreadsheet below, which summarizes monthly data for exemplary schools. The "Full Year Total" column sums data for twelve months, January through December. The formula itself is listed next to the sum result. For example, the formula to summarize results for School 1 is =sum(B7:M7).
Then, for example, you decided that instead of Full Year Total, you want to calculate only results for the first three months, Quarter 1 Total. In order to do that, you will need to summarize only data from January to March. The formula now should say =sum(B7:D7). Basically, letter M should be changed to letter D in the formula. Instead of changing the formula manually, you can do a replace function. It is especially useful when dealing with large data.
In order to replace a letter, text string, or a number in a formula go to file menu and follow this pass: Edit / Replace and then fill in Find what and Replace with. However, if you simply type Find what: M and Replace with: D your results will give you an error and will look like that:
This happened because replace function replaces letter M not only in formula but also in function name "sum", altering it to "sub" and making it invalid.
Also, look at the title. "Total Number of Days missed by students" now says :"Total NuDber of days Dissed by students".
To avoid error messages when replacing a letter, text string, or a number in a formula, you should include a unique character, that is located directly next to what you are replacing, in your replace function. These unique characters will be : ; " ), etc. For example: instead of replacing M with D, you should have replaced :M with :D. You should type find what: :M and replace with: :D. This way replace function will look only for what you specifically identified and will omit others that you do not want to be part of a replace function.
ExcelIdea.com offers consulting services in creating custom solutions or upgrading your existing company spreadsheets. No request is too big or too small. Learn more .
My Blog
My Blog
