TEXT FUNCTIONS

1. TRIM: Removes all spaces from a text string except for single spaces between words

SYNTAX: =TRIM (text)

TEXT: Required. The text from which you want spaces removed.

EXAMPLE:

 

2. LEN: Returns the number of characters in a text string

SYNTAX: =LEN (text)

TEXT: Required. The text whose length you want to find. Spaces count as characters.

EXAMPLE:

3. CHAR: Returns the character specified by the code number from the character set

STNTAX: =CHAR (number)

NUMBER:  Required. A number between 1 and 255 specifying which character you want. The character is from the character set used by your computer.

EXAMPLE:

4. CODE: Returns a numeric code for the first character in a text string

SYNTAX: =CODE (TEXT)

TEXT: Required. The text for which you want the code of the first character.

EXAMPLE:

5. CONCATENATE: Joins several text items into one text item

SYNTAX: =CONCATENATE (text1, [text2],…)

TEXT1: the first item to join. The item can be a text value, number, or cell reference.

TEXT2: Additional text items to join. You can have up to 255 items, up to a total of 8912 characters.

EXAMPLE:

 

6. REPT: Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string

SYNTAX: =REPT (text, number_times)

TEXT: Required. The text you want to repeat.

NUMBER TIMES: Required. A positive number specifying the number of times to repeat Text.

EXAMPLE:

 

7. REPLACE: Replaces part of a text string with a different text string

SYNTAX: =REPLACE (old_text, start_num, num_chars, new_text)

OLD TEXT:  Required. Text in which you want to replace some characters.

START_NUM: Required. The position of the character in old text that you want to replace with new Text.

NUM_CHARS: Required. The number of characters in old text that you want REPLACE to replace with new text.

NEW_TEXT: Required. The text that will replace characters in old text.

EXAMPLE:

 

8.  SUBSTITUTE: Replaces existing text with new text in a text string

SYNTAX: =SUBSTITUTE (text, old_text, new_text, [instance_num])

TEXT: Required. The text or the reference to a cell containing text for which you want to substitute characters.

OLD_TEXT: Required. The text you want to replace.

NEW_TEXT: Required. The text you want to replace old text with.

INSTANCE_NUM: Optional. Specifies which occurrence of old text you want to replace with new   text. If you specify instance num, only that instance of old text is replaced. Otherwise, every occurrence of old text in text is changed to new text.

EXAMPLE:

 

9. TEXT: Converts a value to text in a specific number format

SYNTAX: =TEXT (value, format_text)

EXAMPLE:

10. LEFT: Returns the leftmost characters from a text value

SYNTAX: =LEFT (text, [num_chars])

TEXT: Required. The text string that contains the characters you want to extract.

NUM_CHARS: Optional. Specifies the number of characters you want to extract string from LEFT side.

EXAMPLE:

11. MID: Returns a specific number of characters from a text string, starting at the position you specify

SYNTAX: =MID (text, start_num, num_chars)

TEXT: Required. The text string containing the characters you want to extract.

START_NUM: Required. The position of the first character you want to extract in text. The first character in text has start_num 1, and so on.

NUM_CHARS: Required. Specifies the number of characters you want MID to return from text.

EXAMPLE:

12. RIGHT: Returns the specified number of characters from the end of a text string

SYNTAX: =RIGHT (text, [num_chars])

TEXT:  Required. The text string containing the characters you want to extract.

NUM_CHARS: Optional. Specifies the number of characters you want to extract string from RIGHT side.

EXAMPLE:

 

 DATE FUNCTIONS

1. DAY: Converts a serial number to a day of the month

SYNTAX: =DAY (serial_number)

SERIAL NUMBER: required. The day of the date function you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions

EXAMPLE:

2. MONTH: Converts a serial number to a month

SYNTAX: =MONTH (serial_number)

SERIAL NUMBER: Required. The month of the date function you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions.

EXAMPLE:

3. YEAR: Converts a serial number to a year

SYNTAX: YEAR (serial_number)

SERIAL NUMBER: Required. The year of the date function you want to find. Dates should be entered by using the DATE function, or as results of other formulas or functions.

EXAMPLE:

4. DATE: Returns the serial number of a particular date

SYNTAX: DATE (year, month, day)

YEAR: The year to use when creating the date.

MONTH: The month to use when creating the date.

DAY: The day to use when creating the date.

EXAMPLE:

The Excel DATE function is a built-in function that allows you to create a date with individual year, month, and day components. The DATE function is especially useful when supplying dates as inputs to other functions like SUMIFS or COUNTIFS, since you can easily assemble a date using year, month, and day values that come from a cell reference or formula result.

5. DATE VALUE: Converts a date in the form of text to a number that represents the date in Microsoft excel date-time code

SYNTAX: = DATEVALUE (date_text)

DATE TEXT: Required. Text that represents a date in an Excel date format, or a reference to a cell that contains text that represents a date in an Excel date format.

EXAMPLE:

6. NETWORKDAYS: Returns the number of whole working days excluding holidays between Two days.

SYNTAX: =NETWORKDAYS (start_date, end_date, [holidays])

START_DATE: Required. A date that represents the start date.

END_DATE: Required. A date that represents the end date.

HOLIDAYS: Optional. An optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates.

EXAMPLE:

7. WORKDAY: Returns the next working day of the date before or after a specified number of Days including holidays.

SYNTAX: =WORKDAY (start_date, days, [holidays])

START_DATE: Required. A date that represents the start date.

DAYS: The number of no weekend and no holiday days before or after start date.Positive value for days yields a future date; a negative value yields a past date.

HOLIDAYS: Optional. An optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates.

EXAMPLE:

8. TODAY:Returns the serial number of today’s date

SYNTAX: =TODAY ()

TODAY: The today function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook. It is also useful for calculating intervals

EXAMPLE:

 

TIME FUNCTIONS

1. TIME: Returns the serial number of a particular time

SYNTAX: =TIME (hour, minute, second)

HOUR: The hour for the time you wish to create.

MINUTE: The minute for the time you wish to create.

SECOND: The second for the time you wish to create

EXAMPLE:

The Excel TIME function is a built-in function that allows you to create a time with individual hour, minute, and second components. The TIME function is useful when you want to assemble a proper time inside another formula.

2. TIME VALUE: Converts a time in the form of text to a serial number

SYNTAX: =TIMEVALUE (time_text)

TIME_TEXT: The TIMEVALUE function is a built-in function in Excel that is categorized as a date and time function. The TIMEVALUE function can be entered as part of a formula in a cell of a worksheet

EXAMPLE: 

 

About K.Rajesh:

 

 

 

 

 

 

 

K.Rajesh is a B.Tech(Electronics & Communication Engineering) . Currently he is working as an Analyst Intern with NikhilGuru Consulting Analytics Service LLP, Bangalore.