How to edit a cell of text in Excel ?

What Does It Do ?

This function replaces a specified piece of text with a different piece of text. It can either replace all occurrences of the text, or a specific instance. The function is case sensitive.

Syntax

=SUBSTITUTE(OriginalText,TextToRemove,TextToInsert,InstanceToUse) The InstanceToUse is optional, if it is omitted all instances will be substituted.

Formatting

No special formatting is needed.

Note

To cope with upper or lower case in the substitution you can use other text functions such as =UPPER(), =LOWER() or =PROPER() to ensure that the substitution will take place.

Table 1 shows how differing text cases alter the result of the substitution.

Change The Angle Or Orientation Of Text In Excel 2007

Orientation

1. Type the text into a cell
2. Select the cell
3. On the home tab of the ribbon, look for the alignment group. There you will find a button with an ‘a’ and a ‘b’ and an arrow all on an angle. That’s the Orientation menu. Click on the little drop down arrow beside it.
4. Select one of the quick options to angle your text

If you want to be more specific about the angle of your text, you can select “Format Cell Alignment” and set down to the degree how “slanty” you want your text.

Calculates the difference between two dates in excel

DATEDIF

What Does It Do?

This function calculates the difference between two dates. It can show the result in weeks, months or years.

Syntax

=DATEDIF(FirstDate,SecondDate,"Interval")

FirstDate : This is the earliest of the two dates.

SecondDate : This is the most recent of the two dates.

"Interval" : This indicates what you want to calculate.

These are the available intervals.

"d" Days between the two dates.

"m" Months between the two dates.

"y" Years between the two dates.

"yd" Days between the dates, as if the dates were in the same year.

"ym" Months between the dates, as if the dates were in the same year.

"md" Days between the two dates, as if the dates were in the same month and year.

Formatting

No special formatting is needed.

You can put this all together in one calculation, which creates a text version.

Age is 51 Years, 5 Months and 20 Days

="Age is "&DATEDIF(D34,TODAY(),"y")&" Years, "&DATEDIF(D34,TODAY(),"ym")&" Months and "&DATEDIF(D34,TODAY(),"md")&" Days"

How to use date formula in Excel?

DATE

What Does It Do?

This function creates a real date by using three normal numbers typed into separate cells.

Syntax

=DATE(year,month,day)

Formatting

The result will normally be displayed in the dd/mm/yy format. By using the Format,Cells,Number,Date command the format can be changed.

In Excel Find number of combinations for fixed number of items.

COMBIN

What Does It Do ?

This function calculates the highest number of combinations available based upon a fixed number of items. The internal order of the combination does not matter, so AB is the same as BA.

Syntax

=COMBIN(HowManyItems,GroupSize)

Formatting

No special formatting is required.

Example 1

This example calculates the possible number of pairs of letters available from the four characters ABCD.

Example 2

A decorator is asked to design a colour scheme for a new office. The decorator is given five colours to work with, but can only use three in any scheme. How many colours schemes can be created ?

How to deal with Excel Formula Errors - using ISERROR & IFERROR

ISERROR

What Does It Do ?

This function tests a cell or calculation to determine whether an error has been generated. It will show TRUE for any type of error and FALSE if no error is found.

Syntax

=ISERROR(CellToTest) The CellToTest can be a cell reference or a formula.

Formatting

No special formatting is needed.

Example

The following tables was used to calculate the difference between two dates.

Table 1 shows an error due to the fact that the first entry was entered using an inappropriate date format.

Table 2 shows how the =ISERROR() function has been used to trap the error and inform the user that there has been an error in the data entry.

IFERROR

Syntax

IFERROR(value,value_if_error)

Value_if_error is the value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

Remarks

• If value or value_if_error is an empty cell, IFERROR treats it as an empty string value ("").
• If value is an array formula, IFERROR returns an array of results for each cell in the range specified in value. See the second example below.