How to remove unwanted characters in Ms Excel

The TRIM function returns a string with extra <strong>spaces</strong>, starting spaces and ending spaces removed. The CLEAN function <strong>removes nonprintable characters</strong> from a string.<br /> 1. For example, cell A1 below contains the string " Excel   Easy  "<br /> <br /> <br /> <img alt="Trim Function in Excel" height="110" src="http://www.excel-easy.com/examples/images/remove-unwanted-characters/trim-function.png" title="Trim Function in Excel" width="604" /><br /> Note: the TRIM function returns the string "Excel Easy".<br /> 2. For example, cell A1 below contains a nonprintable character.<br /> <img alt="Clean Function in Excel" height="110" src="http://www.excel-easy.com/examples/images/remove-unwanted-characters/clean-function.png" title="Clean Function in Excel" width="604" /><br /> Note: the CLEAN function removes this nonprintable character. Text imported from other applications may contain nonprintable characters.

• 
• 

1. 1
2. 2

• Previous
• Next

How to remove unwanted characters in Ms Excel

The TRIM function returns a string with extra spaces, starting spaces and ending spaces removed. The CLEAN function removes nonprintable characters from a string. 1. For example, cell A1 …

Read more »

How to use words and number of words on Excel formulas or data

This example describes how to count the <strong>number of words</strong> in a <strong>cell</strong>.<br /> 1a. The TRIM function returns a string with extra spaces, starting spaces and ending spaces removed.<br /> <br /> <div class="separator" style="clear: both; text-align: center;"> <a href="http://www.excel-easy.com/examples/images/number-of-words/trim-function.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://www.excel-easy.com/examples/images/number-of-words/trim-function.png" /></a></div> <br /> 1b. To get the length of the string with normal spaces, we combine the LEN and TRIM function.<br /> <img alt="Len and Trim Function" height="110" src="http://www.excel-easy.com/examples/images/number-of-words/len-trim-function.png" title="Len and Trim Function" width="604" /><br /> 2a. The SUBSTITUTE function replaces existing text with new text in a text string. We use the SUBSTITUTE function to get the string without spaces.<br /> <img alt="Substitute Function" height="110" src="http://www.excel-easy.com/examples/images/number-of-words/substitute-function.png" title="Substitute Function" width="604" /><br /> 2b. To get the length of the string without spaces, we combine the LEN and SUBSTITUTE function.<br /> <img alt="Len and Substitute Function" height="110" src="http://www.excel-easy.com/examples/images/number-of-words/len-substitute-function.png" title="Len and Substitute Function" width="604" /><br /> 3. Now comes the simple trick. To get the number of words, we subtract the length of the string without spaces (10) from the length of the string with normal spaces (12) and add 1.<br /> <img alt="Number of Words" height="110" src="http://www.excel-easy.com/examples/images/number-of-words/number-of-words.png" title="Number of Words" width="604" /><br />

• 
• 
• 
• 
• 

1. 1
2. 2
3. 3
4. 4
5. 5

• Previous
• Next

How to use words and number of words on Excel formulas or data

This example describes how to count the number of words in a cell. 1a. The TRIM function returns a string with extra spaces, starting spaces and ending spaces removed. 1b. To get the length of t…

Read more »

How to calculate the sum Product in MS Excel

To calculate the sum of the products of corresponding numbers in one or more ranges, use <strong>Excel's powerful SUMPRODUCT function</strong>.<br /> 1. For example, the SUMPRODUCT function below calculates the total amount spent.<br /> <br /> <br /> <div class="separator" style="clear: both; text-align: center;"> </div> <div class="separator" style="clear: both; text-align: center;"> <img border="0" src="http://www.excel-easy.com/examples/images/sumproduct/sumproduct-function.png" /> </div> Note: the SUMPRODUCT function performs this calculation: (2 * 1000) + (4 * 250) + (4 * 100) + (2 * 50) = 3500.<br /> 2. The ranges must have the same dimensions or Excel will display the #VALUE! error.<br /> <br /> <div class="separator" style="clear: both; text-align: center;"> <img border="0" src="http://www.excel-easy.com/examples/images/sumproduct/ranges-of-unequal-size.png" /> </div> 3. The SUMPRODUCT function treats any entries that are not numeric as if they were zeros.<br /> <img alt="Non Numeric Entries" height="230" src="http://www.excel-easy.com/examples/images/sumproduct/non-numeric-entries.png" title="Non Numeric Entries" width="604" /><br /> <div class="separator" style="clear: both; text-align: left;"> <br /></div> <br /> <div class="separator" style="clear: both; text-align: left;"> <br /></div>

• 
• 
• 

1. 1
2. 2
3. 3

• Previous
• Next

How to calculate the sum Product in MS Excel

To calculate the sum of the products of corresponding numbers in one or more ranges, use Excel's powerful SUMPRODUCT function. 1. For example, the SUMPRODUCT function below calculates the total…

Read more »

How to Copy Exact formula in Excel

When you copy a formula, <strong>Excel</strong> automatically adjusts the cell references for each new cell the formula is copied to.<br /> For example, cell A3 below contains a formula which adds the value of cell A2 to the value of cell A1.<br /> <br /> <div class="separator" style="clear: both; text-align: center;"> <img border="0" src="http://www.excel-easy.com/examples/images/copy-exact-formula/formula.png" /></div>  When you copy this formula to cell B3 (select cell A3, press CTRL + c, select cell B3, press CTRL + v), the formula will automatically reference the values in column B.<br /> <br /> <div class="separator" style="clear: both; text-align: center;"> <img border="0" src="http://www.excel-easy.com/examples/images/copy-exact-formula/cell-references-change.png" /> </div> <div class="separator" style="clear: both; text-align: center;"> <br /></div> <div class="separator" style="clear: both; text-align: left;"> f you don't want this but instead want to <strong>copy</strong> the <strong>exact formula</strong> (without changing the cell references), execute the following easy steps. </div> 1. Click in the formula bar and select the formula.<br /> <img alt="Copy Exact Formula Example" height="130" src="http://www.excel-easy.com/examples/images/copy-exact-formula/copy-exact-formula-example.png" title="Copy Exact Formula Example" width="346" /><br /> 2. Press CTRL + c, and press Enter.<br /> 3. Select cell B3 and click in the formula bar again.<br /> 4. Press CTRL + v, and press Enter.<br /> Result:<br /> <img alt="Copy Exact Formula Result" height="130" src="http://www.excel-easy.com/examples/images/copy-exact-formula/copy-exact-formula-result.png" title="Copy Exact Formula Result" width="346" /><br /> Both cell A3 and cell B3 contain the exact same formula now.<br /> <div class="separator" style="clear: both; text-align: left;"> <br /></div>

• 
• 
• 
• 

1. 1
2. 2
3. 3
4. 4

• Previous
• Next

How to Copy Exact formula in Excel

When you copy a formula, Excel automatically adjusts the cell references for each new cell the formula is copied to. For example, cell A3 below contains a formula which adds the value of cell A2 …

Read more »

How to Play with DateDif formula in Ms Excel

To get the number of days, weeks or years between two dates, use the <strong>DATEDIF function</strong>. The DATEDIF function has three arguments.<br /> 1. Fill in "d" for the third argument to get the number of days between two dates.<br /> <br /> <div class="separator" style="clear: both; text-align: center;"> <img border="0" src="http://www.excel-easy.com/examples/images/datedif/days.png" /> </div> <div class="separator" style="clear: both; text-align: center;"> <br /></div> Note: =A2-A1 produces the exact same result!<br /> 2. Fill in "m" for the third argument to get the number of months between two dates.<br /> <img alt="Months" height="170" src="http://www.excel-easy.com/examples/images/datedif/months.png" title="Months" width="353" /><br /> 3. Fill in "y" for the third argument to get the number of years between two dates.<br /> <img alt="Years" height="170" src="http://www.excel-easy.com/examples/images/datedif/years.png" title="Years" width="353" /><br /> 4. Fill in "yd" for the third argument to ignore years and get the number of days between two dates.<br /> <img alt="Ignore Years, Get Days" height="170" src="http://www.excel-easy.com/examples/images/datedif/ignore-years-get-days.png" title="Ignore Years, Get Days" width="353" /><br /> 5. Fill in "md" for the third argument to ignore months and get the number of days between two dates.<br /> <img alt="Ignore Months, Get Days" height="170" src="http://www.excel-easy.com/examples/images/datedif/ignore-months-get-days.png" title="Ignore Months, Get Days" width="353" /><br /> 6. Fill in "ym" for the third argument to ignore years and get the number of months between two dates.<br /> <img alt="Ignore Years, Get Months" height="170" src="http://www.excel-easy.com/examples/images/datedif/ignore-years-get-months.png" title="Ignore Years, Get Months" width="353" /><br /> Important note: the DATEDIF function returns the number of <u>complete</u> days, months or years. This may give unexpected results when the day/month number of the second date is lower than the day/month number of the first date. See the example below.<br /> <img alt="Unexpected Result" height="170" src="http://www.excel-easy.com/examples/images/datedif/unexpected-result.png" title="Unexpected Result" width="353" /><br /> The difference is 6 years. Almost 7 years! Use the following formula to return 7 years.<br /> <img alt="Year Function Twice" height="170" src="http://www.excel-easy.com/examples/images/datedif/year-function-twice.png" title="Year Function Twice" width="353" /><br /> <div class="separator" style="clear: both; text-align: left;"> <br /></div>

• 
• 
• 
• 
• 

1. 1
2. 2
3. 3
4. 4
5. 5

• Previous
• Next

How to Play with DateDif formula in Ms Excel

To get the number of days, weeks or years between two dates, use the DATEDIF function. The DATEDIF function has three arguments. 1. Fill in "d" for the third argument to get the number o…

Read more »

Uses of Rand andRandbetween formula in Ms Excel

Excel has two useful functions when it comes to creating <strong>random numbers</strong>. The <strong>RAND</strong> and <strong>RANDBETWEEN</strong> function.<br /> <h3> <a href="https://www.blogger.com/null" id="rand" name="rand"></a>Rand</h3> The RAND function creates a random decimal number between 0 and 1.<br /> 1. Select cell A1.<br /> 2. Type RAND() and press Enter. The RAND function takes no arguments<br /> <br /> <div class="separator" style="clear: both; text-align: center;"> <img border="0" src="http://www.excel-easy.com/examples/images/random-numbers/rand-function.png" /> </div> <div class="separator" style="clear: both; text-align: left;"> <br /></div> <div class="separator" style="clear: both; text-align: left;"> 3. To create a list of random numbers, select cell A1, click on the lower right corner of cell A1 and drag it down.</div> <div class="separator" style="clear: both; text-align: left;"> <br /></div> <div class="separator" style="clear: both; text-align: center;"> <img border="0" src="http://www.excel-easy.com/examples/images/random-numbers/list-of-random-numbers.png" /> </div> <div class="separator" style="clear: both; text-align: center;"> <br /></div> Note that cell A1 has changed. That is because random numbers change every time a cell on the sheet is calculated.<br /> 4. If you don't want this, simply copy the random numbers and paste them as values.<br /> <br /> <div class="separator" style="clear: both; text-align: center;"> <img border="0" src="http://www.excel-easy.com/examples/images/random-numbers/paste-as-values.png" /> </div> <div class="separator" style="clear: both; text-align: center;"> <br /></div> <div class="separator" style="clear: both; text-align: left;"> 5. Select cell C1 and look at the formula bar. This cell holds a value now and not the RAND function.</div> <div class="separator" style="clear: both; text-align: left;"> <br /></div> <div class="separator" style="clear: both; text-align: center;"> <a href="http://www.excel-easy.com/examples/images/random-numbers/values.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://www.excel-easy.com/examples/images/random-numbers/values.png" /></a></div> <div class="separator" style="clear: both; text-align: left;">  </div> <br /> <h3> Randbetween</h3> The RANDBETWEEN function returns a random whole number between two boundaries.<br /> 1. Select cell A1.<br /> 2. Type RANDBETWEEN(50,75) and press Enter.<br /> <img alt="Randbetween function" src="http://www.excel-easy.com/examples/images/random-numbers/randbetween-function.png" title="Randbetween function" /><br /> <div class="separator" style="clear: both; text-align: left;"> <br /></div> <div class="separator" style="clear: both; text-align: left;"> <br /></div> 3. If you want to create random decimal numbers between 50 and 75, modify the RAND function as follows:<br /> <img alt="Modified Rand function" src="http://www.excel-easy.com/examples/images/random-numbers/modified-rand-function.png" title="Modified Rand function" /><br /> <div class="separator" style="clear: both; text-align: left;"> <br /></div> <div class="separator" style="clear: both; text-align: left;">  </div>

• 
• 
• 
• 
• 

1. 1
2. 2
3. 3
4. 4
5. 5

• Previous
• Next

Uses of Rand andRandbetween formula in Ms Excel

Excel has two useful functions when it comes to creating random numbers. The RAND and RANDBETWEEN function. Rand The RAND function creates a random decimal number between 0 and 1. 1. Sel…

Read more »

How to change negative number into zero using Max formula in Ms Excel

A clever <b>Excel</b> trick to change <b>negative numbers to zero</b> but leave positive numbers unchanged.<br /> Below you can find two columns with numbers. To subtract the numbers in column B from the numbers in column A, execute the following steps.<br /> 1. Select cell C1.<br /> 2. Enter the formula =A1-B1<br /> 3. Click the lower right corner of cell C1 and drag the formula down.<br /> <div class="separator" style="clear: both; text-align: center;"> <img border="0" src="http://www.excel-easy.com/examples/images/negative-numbers-to-zero/negative-numbers-to-zero-example.png" /> </div> <div class="separator" style="clear: both; text-align: center;"> Result:</div> <div class="separator" style="clear: both; text-align: center;"> <img border="0" height="320" src="http://www.excel-easy.com/examples/images/negative-numbers-to-zero/negative-numbers.png" width="316" /></div> <div class="separator" style="clear: both; text-align: center;"> <br /></div> <br /> 4. To change the negative numbers to zero but leave the positive numbers unchanged, instead of =A1-B1, use =MAX(0,A1-B1) and drag the function down.<br /> Result:<br /> <div class="separator" style="clear: both; text-align: center;"> <a href="http://www.excel-easy.com/examples/images/negative-numbers-to-zero/negative-numbers-to-zero-result.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="http://www.excel-easy.com/examples/images/negative-numbers-to-zero/negative-numbers-to-zero-result.png" width="316" /></a></div>

• 
• 
• 

1. 1
2. 2
3. 3

• Previous
• Next

How to change negative number into zero using Max formula in Ms Excel

A clever Excel trick to change negative numbers to zero but leave positive numbers unchanged. Below you can find two columns with numbers. To subtract the numbers in column B from the numbers in colu…

Read more »