USE THE LEFT, MID AND RIGHT FUNCTIONS TO EXTRACT DATE PARTS USING MICROSOFT EXCEL

The Microsoft Excel worksheet in Figure 4-8 contains date values in column A. Excel cannot interpret these values as dates. To show the date in a correct format, the values of column A have to be extracted to year, month, and day. 

To extract, combine, and display the correct format:

1. Select cells B2:B10 and type the following formula: =DATE(LEFT(A2,4),MID(A2,FIND(".",A2,1)+1,2), RIGHT(A2,2)).
2. Press <-Ctrl+Enter->.

USE THE LEFT, MID AND RIGHT FUNCTIONS TO EXTRACT DATE PARTS USING MICROSOFT EXCEL

Note: The first four digits have to be transferred with the LEFT function. Then use the FIND function to detect the decimal point. On the right of the first decimal point (+1), two digits are interpreted as the month using the MID function. On the right side of the second decimal point, use the RIGHT function to extract two digits as the day value.

USE THE DATE FUNCTION TO COMBINE COLUMNS WITH DATE PARTS USING MICROSOFT EXCEL

The Excel worksheet shown in Figure 4-7 uses three columns showing dates. Column A lists years, column B lists months using numbers from 1 to 12, and column C contains the days of a month from 1 to 31. These columns have to be combined to show one formatted date. 

To do so, use the DATE function.

DATE(year, month, day)

year: This argument can be from one to four digits. Microsoft Excel for Windows uses the 1900 date system. 

month: A number representing the month of the year (1 to 12).
day: A number representing the day of the month (1 to 31).

To combine values of cells into one date:

1. Select cells D2:D10 and type the following formula:=DATE(A2,B2,C2).
2. Press <-Ctrl+Enter->.

Note that Excel knows which years are leap years, and thus provides correct results even when incorrect data is entered, as in row 3.

USE THE DATE FUNCTION TO COMBINE COLUMNS WITH DATE PARTS USING MICROSOFT EXCEL

USE THE NOW FUNCTION TO CALCULATE THE TIME USING MICROSOFT EXCEL

To calculate with time using Now() Function in Microsoft Excel, it is helpful to know that Excel stores the time as a decimal value. For example, 0.5 is 12:00 noon, 0.75 is 18:00, and so on. 

To calculate with time:

1. In cell B1 type the formula =NOW().
2. In cell B2 type the formula =B1+0.25 to add six hours to the current time in cell B1.

3. Type the following formula in cell C1: ="The meeting starts at " & TEXT(B1,"hh:mm") & " and ends at " & TEXT(B2,"hh:mm").

4. Press <-Enter->.

USE THE NOW FUNCTION TO CALCULATE THE TIME USING MICROSOFT EXCEL

USE THE NOW FUNCTION TO SHOW THE CURRENT TIME USING MICROSOFT EXCEL

The previous tip described how to get the current date. Now we want to determine the current time in microsoft excel. The NOW function in Microsoft Excel returns the serial number of the current date and time. Microsoft Excel stores dates as sequential numbers so they can be used in calculations. By default, January 1, 1900, is number 1, and January 1, 2006, is number 38718 because it is 38,717 days after January 1, 1900. Numbers to the right of the decimal point in the number represent the time; numbers to the left represent the date. For example, the serial number .5 represents the time 12:00 noon. The NOW() function is not updated continuously.

To show the current time:

1. In cell A1 type the formula =NOW() and press <-Enter->.
2. Ensure that cell A1 is selected and choose Cells from the Format menu.
3. In the Number tab, select Date under Category.
4. Select the format 3/14/01 1:30 PM.
5. Press <-Enter->

USE THE NOW FUNCTION TO SHOW THE CURRENT TIME USING MICROSOFT EXCEL

USE THE TEXT FUNCTION TO CALCULATE WITH THE TODAY FUNCTION USING MICROSOFT EXCEL

How to use the Text Function to calculate with the Today Function in Microsoft Excel? A project starts today and ends 10 days later. These dates are shown in cells B1 and B2. The end date has to be calculated based on the start date, and the dates have to be combined with additional text to form the message shown in cell A4. It can be used for project duration calculation also.

To calculate with the TODAY function:

1. In cell B1 type the formula =TODAY().
2. In cell B2 type the formula =TODAY()+10 to add ten days to the current date.

3. Select cell A4 and type the following formula: ="The project starts on " & TEXT(B1,"MM/DD/YYYY") & " and ends on " & TEXT(B2,"MM/DD/YYYY").

4. Press <-Enter->.

USE THE TEXT FUNCTION TO CALCULATE WITH THE TODAY FUNCTION USING MICROSOFT EXCEL

Note: The TEXT function (TEXT(value, format_text)) converts a value to text in a specific number format. In this example, format_text is shown as MM = month (two digits), DD = day(two digits), and YYYY = year (four digits).

USE THE TODAY FUNCTION TO CHECK FOR FUTURE DATES USING MICROSOFT EXCEL

How to Check for Future Dates? In a worksheet, dates in column A have to be checked to see if they are in the future. The actual date can be determined by using the TODAY function in Microsoft Excel and can be compared with the dates in the worksheet using the IF function of Microsoft Excel. If dates are in the future, the result in column B should be Y; otherwise, it should be N. 

To check for future dates:

1. In cell D1, type the formula =TODAY() to show the current date.
2. Select cells B2:B10 and type the following formula: =IF(A2<=TODAY(),"n","y").
3. Press <-Ctrl+Enter->.

USE THE TODAY FUNCTION TO CHECK FOR FUTURE DATES USING MICROSOFT EXCEL

USE THE WEEKDAY FUNCTION TO DETERMINE THE WEEKEND USING MICROSOFT EXCEL

How do you find out whether or not a date falls on a weekend? To answer this question you can either use the previous tip or use the more convenient WEEKDAY function in Microsoft Excel. This function returns the day of the week as a number corresponding to a date. The returned number is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default. 

To determine the weekend:

1. Select cells B2:B10 and type the formula =A2.
2. Press <-Ctrl+Enter->.
3. From the Format menu, select Cells.
4. Select the Number tab and click on Custom below Category.
5. In the Type box, change the number format to "dddd".
6. Press OK.
7. Select cells C2:C10 and type the following formula: =IF(OR(WEEKDAY(A2)=7,WEEKDAY(A2)=1),"weekend","").
8. Press <-Ctrl+Enter->.

USE THE WEEKDAY FUNCTION TO DETERMINE THE WEEKEND USING MICROSOFT EXCEL

Note: In column B use the custom format dddd to check the result of column C.

USE THE CUSTOM FORMATTING TO DISPLAY DAY OF THE WEEK USING MICROSOFT EXCEL

How to show the day of the week in Microsoft Excel. A worksheet contains dates in Column A. Use this tip to get the corresponding day of the week of these dates.

To display weekdays using customized formatting:

1. Select cells B2:B10 and type the formula =A2.
2. Press <-Ctrl+Enter->.
3. From the Format menu, select Cells.
4. Select the Number tab and click on Custom below Category.
5. In the Type box, change the number format to "dddd".
6. Press OK.

USE THE CUSTOM FORMATTING TO DISPLAY DAY OF THE WEEK USING MICROSOFT EXCEL

USE THE TEXT FUNCTION TO COMBINE AND FORMAT TEXT USING MICROSOFT EXCEL

In a daily sales record, employee names are listed in column A and their daily sales are entered in column B. There are two tasks here: We need to determine the percentage of the weekly sales goal ($1,000) that was met by the daily sales, and we want to combine the information from columns A and B.

USE THE TEXT FUNCTION TO COMBINE AND FORMAT TEXT USING MICROSOFT EXCEL

To combine and format text:

1. In a worksheet, copy the data shown in Figure 3-30.
2. Select cells C2:C10 and type the formula =B2/1000.
3. Press <-Ctrl+Enter->.

4. Select cells D2:D10 and type the following formula: =A2 & " sold " & TEXT(B2,"$0.00") & "today. That's " & TEXT(C2,"0.0%") & " of weekly goal.

5. Press <-Ctrl+Enter->.

USE THE TEXT FUNCTION TO COMBINE AND FORMAT TEXT USING MICROSOFT EXCEL

USE THE T FUNCTION TO CHECK FOR VALID NUMBERS USING MICROSOFT EXCEL

How to check whether a cell contains a number or text in Microsoft Excel? Take a look at Figure 3-29. Notice that some numbers are listed, but there are also references to text and other values. You can check whether a number is a real value in an Excel worksheet by using the T() function in Microsoft Excel. This function checks whether a value is text. If it is text, T returns the text; if it is not, T returns empty text. 

To check for valid numbers:

1. Enter some values in column A and change the format for some of them to text (using the Cells option from the Format menu).

2. Select cells B2:B10 and type the following formula: =T(A2).
3. Press <-Ctrl+Enter->.

USE THE T FUNCTION TO CHECK FOR VALID NUMBERS USING MICROSOFT EXCEL

USE THE DOLLAR FUNCTION TO CONVERT NUMBERS TO CURRENCY IN TEXT FORMAT USING MICROSOFT EXCEL

The DOLLAR function of Microsoft Excel converts a number to text format and applies a currency symbol. The currency format will be rounded to the specified decimal place.

 DOLLAR(number, decimals)

number: A number, a reference to a cell that contains a number, or a formula that calculates a value. decimals: The number of digits to the right of the decimal point.

If negative, number is rounded to the left of the decimal point.

If omitted, Excel sets it to 2.

To convert numbers to currency:
1. In cells A2:A10, type numeric values.
2. Select cells B2:B10 and type the following formula: =DOLLAR(A2,2).
3. Press <-Ctrl+Enter->.

USE THE DOLLAR FUNCTION TO CONVERT NUMBERS TO CURRENCY IN TEXT FORMAT USING MICROSOFT EXCEL

USE THE CODE FUNCTION TO DETERMINE THE NUMERIC CODE OF A CHARACTER USING MICROSOFT EXCEL

To return the numeric, or ASCII, code for the first character in a text string, use the CODE function in Microsoft Excel. This function returns the code corresponding to the currently used character set. 

To determine the numeric code of a character:

1. In cells A2:A10, type letters of the alphabet, both uppercase and lowercase.
2. Select cells B2:B10 and type the following formula: =CODE(A2).
3. Press <-Ctrl+Enter->.

USE THE CODE FUNCTION TO DETERMINE THE NUMERIC CODE OF A CHARACTER USING MICROSOFT EXCEL

USE THE CHAR FUNCTION TO DETERMINE SPECIAL CHARACTERS USING MICROSOFT EXCEL

To use special characters, it is necessary to figure out how to get them. The CHAR function in Microsoft Excel will return the character specified by a number in column A. Note that some fonts may have different special characters.  

To determine special characters:

1. Copy Column A as shown below to your worksheet.
2. Select cells B2:B16 and type the following formula:=CHAR(A2).
3. Press <-Ctrl+Enter->.

USE THE CHAR FUNCTION TO DETERMINE SPECIAL CHARACTERS IN MICROSOFT EXCEL

USE THE CHAR FUNCTION TO CHECK YOUR FONTS USING MICROSOFT EXCEL

To check a few fonts at the same time, open a new worksheet and format columns B to E with the Arial, Wingdings, Webdings, and Terminal fonts. Use the CHAR function of Microsoft Excel to return the character specified by a number in Column A.  

To check installed fonts:

1. In cell A2, type 1.
2. Press <-Ctrl-> and drag the right corner of cell A2 down to cell A256.
3. Select cells B2:E256 and type the following formula: =CHAR($A2).
4. Press <-Ctrl+Enter->.

USE THE CHAR FUNCTION TO CHECK YOUR FONTS USING MICROSOFT EXCEL

USE THE REPT FUNCTION TO SHOW DATA IN A CHART USING MICROSOFT EXCEL

To show data in a chart-like view (Showing Percentage of Progress in graphical form in Excel Cell), you can define a character and repeat this character a specified number of times using the REPT text function in Microsoft Excel. 

To show data in a chart:

1. In cells B2:B10, type percentages in the range of 1% to 100%.
2. Select cells C2:C10 and type the following formula: =REPT("|",B2*100).
3. Press <-Ctrl+Enter->.

USE THE REPT FUNCTION TO SHOW DATA IN A CHART USING MICROSOFT EXCEL

USE THE REPT FUNCTION TO SHOW DATA IN GRAPHICS MODE USING MICROSOFT EXCEL

To demonstrate data in a chart-like view, you can use a special character in a symbol font and repeat the character. To do so, use the REPT text function of Microsoft Excel. This function repeats a character a given number of times. 

To show data in a simple chart:

1. In cells A2:A10, type numbers from 1 to 10.
2. Select cells B2:B10 and type the following formula: =REPT("n",A2).
3. Press <-Ctrl+Enter->. 4. From the Format menu, select Cells.
5. Select the Font tab.
6. Select Wingdings from the Font list and click OK.

USE THE REPT FUNCTION TO SHOW DATA IN GRAPHICS MODE USING MICROSOFT EXCEL

USE THE CLEAN TEXT FUNCTION TO REMOVE ALL NONPRINTABLE CHARACTERS USING MICROSOFT EXCEL

If data is imported from other applications, it is possible for this data to contain characters that may not be printable. In this case, the CLEAN text function of Microsoft Excel can be used to remove all nonprintable characters from text. 

To delete nonprintable characters:

1. Type any text in cells A2:A5. Make sure that some of the cells contain nonprintable characters.
2. Select cells A2:A5 and type the following formula: =CLEAN(A2).
3. Press <-Ctrl+Enter->.

USE THE CLEAN TEXT FUNCTION TO REMOVE ALL NONPRINTABLE CHARACTERS USING MICROSOFT EXCEL

USE THE TRIM FUNCTION TO CONVERT "TEXT-NUMBERS" TO REAL NUMBERS USING MICROSOFT EXCEL

In this example numbers entered as text have to be converted to values. To do this, use the VALUE and TRIM Text functions in combination to get the correct result. The VALUE function converts a text string that represents a number to a number, and the TRIM function deletes all leading and trailing spaces.  

To convert text that represents a number to a value:

1. Format column A as text.
2. In cells A2:A10, type a series of numbers with leading spaces.
3. Select cells B2:B10 and type the following formula: =VALUE(TRIM(A2)).
4. Press <-Ctrl+Enter->.

USE THE TRIM FUNCTION TO CONVERT "TEXT-NUMBERS" TO REAL NUMBERS USING MICROSOFT EXCEL

USE THE TRIM FUNCTION TO DELETE SPACES IN MICROSOFT EXCEL

Column A of a worksheet contains text with spaces at the left and right side of the text. This could be a problem if, for example, data is used for evaluation.  Use the TRIM Text function to remove all spaces from a text string except for the single spaces between words. 

 To delete unneeded spaces from text:

1. In cells A2:A5, type text with leading and trailing spaces.
2. Select cells B2:B5 and type the following formula: =TRIM(A2).
3. Press <-Ctrl+Enter->.

USE THE TRIM FUNCTION TO DELETE SPACES IN MICROSOFT EXCEL

USE THE FIXED FUNCTION TO ROUND AND CONVERT NUMBERS TO TEXT USING MICROSOFT EXCEL

To round numbers and return the result as text, use the FIXED function. This function rounds a number to the specified number of decimals, returning the result as text with or without commas.

FIXED(number, decimals, no_commas)

number: The number to round and convert to text.
decimals: The number of digits to the right of the decimal point. If omitted, Excel sets it to 2.

no_commas: A logical value that prevents FIXED from including commas when set to TRUE. If no_commas is FALSE or omitted, the returned text includes commas.

To round and convert numbers to text:

1. In cells A2:A10, type values with decimals.
2. Select cells B2:B10 and type the following formula: =FIXED(A2,-1,FALSE).
3. Press <-Ctrl+Enter->.
4. Select cells C2:C10 and type the following formula: =FIXED(A2,-2,FALSE).
5. Press <-Ctrl+Enter->.

USE THE FIXED FUNCTION TO ROUND AND CONVERT NUMBERS TO TEXT USING MICROSOFT EXCEL

USE THE PROPER FUNCTION TO CONVERT INITIAL CHARACTERS FROM LOWERCASE TO UPPERCASE USING MICROSOFT EXCEL

To convert the first letter in each word to uppercase and all other letters to lowercase, the PROPER function is used. This function capitalizes the first letter in a text string and any letters that follow characters other than a letter (such as a space). All other letters will be changed to lowercase.

This function has the following syntax:

PROPER(text)

text: Text enclosed in quotation marks, a formula that returns text, or a reference to a cell that contains the text that should have an initial capital letter.

To convert a text string to proper case:

1. In cells A2:A7 type any kind of text.
2. Select cells B2:B7 and type the following formula: =PROPER(A2).
3. Press <-Ctrl+Enter->.

USE THE PROPER FUNCTION TO CONVERT INITIAL CHARACTERS FROM LOWERCASE TO UPPERCASE USING MICROSOFT EXCEL

USE THE LOWER FUNCTION TO CONVERT TEXT FROM UPPERCASE TO LOWERCASE USING MICROSOFT EXCEL

To convert all letters to lowercase in a text string, use the LOWER function. This function has the following syntax:

LOWER(text)

text: Text to be converted to all lowercase letters. The text can be either a reference or a text string.

To convert a text string to lowercase:

1. In cells A2:A8 type any text in uppercase letters.
2. Select cells B2:B8 and type the following formula: =LOWER(A2).
3. Press <-Ctrl+Enter->.

USE THE LOWER FUNCTION TO CONVERT TEXT FROM UPPERCASE TO LOWERCASE USING MICROSOFT EXCEL

USE THE UPPER FUNCTION TO CONVERT TEXT FROM LOWERCASE TO UPPERCASE USING MICROSOFT EXCEL

To convert a text string to all uppercase letters, the UPPER function is used. This function has the following syntax:

UPPER(text)

text: Text to be converted to all uppercase letters. The text can be either a reference or a text string.

To convert a text string to uppercase:

1. In cells A2:A8 type any text in lowercase letters.
2. Select cells B2:B8 and type the following formula: =UPPER(A2).
3. Press <-Ctrl+Enter->.

USE THE UPPER FUNCTION TO CONVERT TEXT FROM LOWERCASE TO UPPERCASE USING MICROSOFT EXCEL

USE THE FIND FUNCTION TO COMBINE TEXT AND TIME USING MICROSOFT EXCEL

The following worksheet contains daily tasks in column A and their corresponding dates in column B. The task here is to combine the data and change the format of the dates. Take a closer look at the following screenshot:

USE THE FIND FUNCTION TO COMBINE TEXT AND TIME USING MICROSOFT EXCEL

The text string XXX has to be replaced by the dates in column B. To do so, the starting position of the text string has to be determined by using the FIND function. The REPLACE function will replace the XXX text string with the date.

FIND(find_text, within_text, start_num)

find_text: Text to find. Wildcard characters are not allowed.
within_text: Text containing find_text.
start_num: Specifies the first character in the search. If omitted, Excel sets start_num to 1.

To combine and format data at the same time:

1. In a worksheet, copy the data shown in Figure 3-14.
2. Select cells C2:C6 and type the following formula: =REPLACE(A2,FIND("XXX",A2,1),3,TEXT(B2,"MM-DD-YYYY")).
3. Press <-Ctrl+Enter->.

USE THE FIND FUNCTION TO COMBINE TEXT AND TIME USING MICROSOFT EXCEL

USE THE REPLACE FUNCTION TO REPLACE AND CALCULATE USING MICROSOFT EXCEL

The following worksheet contains an employee’s work hours.

USE THE REPLACE FUNCTION TO REPLACE AND CALCULATE USING MICROSOFT EXCEL

The format of columns B and D cannot be used to calculate time. Note that the triangle in the upper-left corner indicates the numbers have been entered as text. Rather than a period, a colon needs to be placed between the numbers to indicate time. Therefore, the period has to be replaced using the REPLACE function in combination with SEARCH. The REPLACE function replaces part of a text string with a different text string, based on the number of characters specified. The syntax for the SEARCH function is provided earlier in this chapter.

REPLACE(old_text, start_num, num_chars, new_text)

old_text: Original text in which some characters are to be replaced.
start_num: Position of the character in old_text that is to be replaced with new_text.
num_chars: Number of characters in old_text to be replaced.
new_text: Text that will replace characters in old_text.

To replace periods with colons and calculate:

1. In a worksheet, copy the data shown in Figure 3-12.
2. Select cells D2:D10 and type the following formula: =(REPLACE(C2,SEARCH(".",C2),1,":")-REPLACE(B2,SEARCH(".",B2),1,":")).
3. Press <-Ctrl+Enter->.

USE THE REPLACE FUNCTION TO REPLACE AND CALCULATE USING MS EXCEL

USE THE SUBSTITUTE FUNCTION TO COMBINE AND SEPARATE USING MICROSOFT EXCEL

To combine several columns into one column, the & operator is used. To include a separator between each part in addition to blank spaces, here is a way to specify the separator just once while using the SUBSTITUTE function.

To combine and separate at the same time:

1. In columns A to E, type any kind of data.
2. Select cells F2:F10 and type the following formula: =SUBSTITUTE(A2&" "&B2&" "&C2&" "&D2 & " "&E2," "," - ").
3. Press <-Ctrl+Enter->.

USE THE SUBSTITUTE FUNCTION TO COMBINE AND SEPARATE USING MICROSOFT EXCEL

USE THE SUBSTITUTE FUNCTION TO REMOVE WORD-WRAPPINGS IN CELLS USING MICROSOFT EXCEL

To wrap text in a cell, you can select Cells from the Format menu, select the Alignment tab, and activate the Wrap text check box. Another way to do this is to type the first row of a cell, then press <-Alt+Enter->, type the next line, and continue as desired.

If you want to disable word-wrap, the SUBSTITUTE and CHAR functions can be used together. CHAR returns the character specified by a number. The ASCII character numerical equivalent for word-wrap is 10.

To delete word-wrap:

1. In cells A2 and A3 type text with word-wraps.
2. Select cells B2:B3 and type the following formula: =SUBSTITUTE(A2,CHAR(10)," ").
3. Press <-Ctrl+Enter->.

USE THE SUBSTITUTE FUNCTION TO REMOVE WORD-WRAPPINGS IN CELLS USING MICROSOFT EXCEL

USE THE SUBSTITUTE FUNCTION TO CONVERT NUMBERS TO WORDS USING MICROSOFT EXCEL

A worksheet contains the numbers 1 to 5 in column A. Use the SUBSTITUTE function to change each number to a word. For example, change 1 to one, 2 to two, 3 to three, 4 to four, and 5 to five.

To convert each number to a word:

1. In column A, type a series of numbers using 1, 2, 3, 4, and 5.
2. Select cells B2:B10 and type the following formula: =(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(SUBSTITUTE(SUBSTITUTE(A2,1,"one-"),2,"two-"),3,"three-"),4,"four-"),5,"five-")).
3. Press <-Ctrl+Enter->.

USE THE SUBSTITUTE FUNCTION TO CONVERT NUMBERS TO WORDS USING MICROSOFT EXCEL

USE THE SUBSTITUTE FUNCTION TO SUBSTITUTE PARTS OF A CELL USING MICROSOFT EXCEL

In this example, the “-” character needs to be replaced with a blank space. But only the first occurrence of this character should be replaced. To do this, type any kind of text and numbers in column A as shown in the screenshot below, using the “-” character in different positions and in a variety of occurrences.

To substitute parts of a cell:

1. Select cells B2:B10 and type the following formula: =SUBSTITUTE(A2,"-","",1).
2. Press <-Ctrl+Enter->.

USE THE SUBSTITUTE FUNCTION TO SUBSTITUTE PARTS OF A CELL USING MICROSOFT EXCEL

Note: If you want to substitute the second occurrence of this character, use the following formula: =SUBSTITUTE(A2,"-","",2).

USE THE SUBSTITUE FUNCTION TO SUBSTITUTE CHARACTERS USING MICROSOFT EXCEL

A worksheet contains values in column A that cannot be summed up because the first character in each cell is an apostrophe. How do you solve this problem? Use the SUBSTITUTE formula to replace specific characters in text or a cell.

SUBSTITUTE(text, old_text, new_text, instance_num)

text: The text or the reference to a cell containing text in which characters are substituted.
old_text: Text that should be replaced.
new_text: Text that replaces old_text.

instance_num: Specifies which instance of old_text is to be replaced by new_text. If omitted, every instance of old_text is replaced.

To use SUBSTITUTE and force Excel to calculate:

1. Format column A as text.

2. Enter a series of numbers in cells A2:A10. Notice that Excel tags them with green triangles in the upper-left

corner to indicate the numbers have been entered as text.
3. Select cells B2:B10 and type the following formula: =VALUE(SUBSTITUTE(A2,"'","")).
4. Press <-Ctrl+Enter->.
5. Select cell A12, type the following formula: =SUM(A2:A10), and press <-Enter->.
6. Select cell B12, type the following formula: =SUM(B2:B10), and press <-Enter->.

USE THE SUBSTITUTE FUNCTION TO SUBSTITUTE CHARACTERS USING MICROSOFT EXCEL

USE THE EXACT FUNCTION TO COMPARE TWO COLUMNS USING MICROSOFT EXCEL

There are two ways to compare two columns. With the IF function, it doesn’t matter if the text is written in upper- or lowercase. The EXACT function, on the other hand, can distinguish between upper- and lowercase.

EXACT(text1, text2)
text1: The first text string.
text2: The second text string.

To compare two columns:

1. In a worksheet, copy columns A and B from Figure 3-6.
2. Select cells C2:C10 and type the following formula: =EXACT(A2,B2).
3. Press <-Ctrl+Enter->.
4. Select cells D2:D10 and type the following formula: =IF(A2=B2,TRUE,FALSE).
5. Press <-Ctrl+Enter->.

USE THE EXACT FUNCTION TO COMPARE TWO COLUMNS USING MICROSOFT EXCEL

Note: Differences in formatting don’t matter. The function will still work, as shown in cells A7 and B7; B7 contains the numeric value of the date in A7. Extraneous blanks in cells, as shown in row 8, also don’t matter.

USE THE MID FUNCTION TO SUM THE DIGITS OF A NUMBER USING MICROSOFT EXCEL

A worksheet contains four-digit numbers in column A. Each of the four digits has to be added and the result shown in column B. To do so, the four digits of a cell are extracted by the MID function and summed.

To determine the cross sum:

1. In a worksheet, enter a series of four-digit numbers in cells A2:A10.
2. Select cells B2:B10 and type the following formula: =MID(A2,1,1)+MID(A2,2,1)+MID(A2,3,1)+MID(A2,4,1).
3. Press <-Ctrl+Enter->.

USE THE MID FUNCTION TO SUM THE DIGITS OF A NUMBER USING MICROSOFT EXCEL

USE THE MID FUNCTION TO SEPARATE LAST NAME FROM FIRST NAME USING MICROSOFT EXCEL

In a worksheet, names are listed in column A, and the last name has to be copied to column B. As in the previous example, the space between the first and last names has to be determined with the SEARCH function. This function returns the position of the desired character inside a text string starting from start_num. The MID function then returns a specific number of characters starting from a desired position inside a text string. 

MID(text, start_num, num_chars)

text: Text string containing the desired characters.
start_num: Position of the first character to extract from the text.
num_chars: Number of characters to be extracted.

To separate the last name from the first name:

1. In a worksheet, enter a series of full names in cells A2:A11.
2. Select cells B2:B11 and type the following formula: =MID(A2,SEARCH(" ",A2)+1,100).
3. Press <-Ctrl+Enter->.

USE THE MID FUNCTION TO SEPARATE LAST NAME FROM FIRST NAME USING MICROSOFT EXCEL

USE THE SEARCH FUNCTION TO SEPARATE FIRST NAME FROM LAST NAME USING MICROSOFT EXCEL

This task demonstrates how to separate first and last names. In a worksheet, full names are listed in column A. We want to copy the first name to column B. The SEARCH function can be used to determine the space between the parts of the text string. This function returns the position of the searched character inside a text

string.

SEARCH(find_text, within_text, start_num)

find_text: The text or character for which you are searching. Wildcard characters, question marks (?), and asterisks (*) can be used in find_text. A question mark matches any single character, and an asterisk matches any sequence of characters. To find a question mark or asterisk, type a tilde (~) before the character.

within_text: The text you want to search for find_text.

start_num: The start position for the search function within text; if there is no start_num defined inside the function, Excel sets it to 1.

To separate the first and last names:

1. In a worksheet, enter a series of full names in cells A2:A11.
2. Select cells B2:B11 and type the following formula: =LEFT(A2,SEARCH(" ",A2)-1).
3. Press <-Ctrl+Enter->.

USE THE SEARCH FUNCTION TO SEPARATE FIRST NAME FROM LAST NAME

USE THE LEFT FUNCTION TO CONVERT INVALID NUMBERS TO VALID NUMBERS USING MICROSOFT EXCEL

In this example, invalid numbers have to be converted to valid numbers. The invalid numbers contain a minus sign at the right end of the text. Excel cannot interpret this, so the last digit of the text needs to be moved to the left of the numbers. First, check the length of each number with the LEN function. This function returns the number of characters in a text string. Then use the LEFT function to move the minus sign.

LEN(text)

text: The text whose length you want to be determined. A space is considered a character.

To cut off the last digit and display a negative value:

1. In a worksheet, enter a series of numbers in cells A2:A10 that have a minus sign at the end.
2. Select cells B2:B10 and type the following formula: =-LEFT(A2,LEN(A2)-1).
3. Press <-Ctrl+Enter->.

USE THE LEFT FUNCTION TO CONVERT INVALID NUMBERS TO VALID NUMBERS USING MICROSOFT EXCEL

USE THE LEFT AND RIGHT FUNCTIONS TO SEPARATE A TEXT STRING OF NUMBERS USING MICROSOFT EXCEL

A worksheet contains a list of 10-digit numbers that have to be separated into two parts: a three-digit part and a seven-digit part. Use the LEFT and RIGHT functions to do this. The LEFT function returns the first character or characters in a text string, based on the number of characters specified. The RIGHT function returns the last character or characters in a text string based on the number of characters specified.  Text Functions.

To separate a text string of numbers:

1. In a worksheet, enter a series of 10-character numbers in cells A2:A10. The numbers can also contain letters.

2. Select cells B2:B10 and type the following formula: =LEFT(A2,3).
3. Press <-Ctrl+Enter->.
4. Select cells C2:C10 and type the following formula: =RIGHT(A2,7).
5. Press <-Ctrl+Enter->.

USE THE LEFT AND RIGHT FUNCTIONS TO SEPARATE A TEXT STRING OF NUMBERS USING MICROSOFT EXCEL

USE THE IF FUNCTION TO CREATE YOUR OWN TIMESHEET USING MICROSOFT EXCEL

In the last example for this chapter we create our own timesheet, step by step. First, press <-Shift+F11-> to insert a new worksheet.  Then create the following timesheet as an example:

USE THE IF FUNCTION TO CREATE YOUR OWN TIMESHEET USING MICROSOFT EXCEL

We have to consider that the daily target of eight hours is still fulfilled when an employee is ill (IL), on holiday (HO), or in training (TR). For other days, the number of working hours has to be calculated.

To calculate the daily working hours:

1. Select cell F2:F6 and type the following formula: =IF(OR(C2="TR",C2="IL",C2="HO"),E2,D2-C2).
2. Press <-Ctrl+Enter->.

USE THE IF FUNCTION TO CREATE YOUR OWN TIMESHEET USING MICROSOFT EXCEL

Microsoft Excel Tutorials, MS Excel Tutorials, Logical Functions in Excel, IF Function in Excel, Use the IF function to create your own timesheet using MS Excel, Video Tutorials of Microsoft Excel, Advanced Functions in Excel, Formulas in Excel, Using Macros & VBA Codes in Excel

USE THE IF FUNCTION TO CHECK WHETHER A DATE IS IN THE PAST OR FUTURE USING MICROSOFT EXCEL

In this example we want to check whether a particular date is in the past or the future. To do so, the TODAY() function is used with IF to compare dates with the actual date and show its result.

To compare dates — variant A:

1. Select cell B2:B11 and type the following formula: =IF(NOT(A2>TODAY()),"past","future").
2. Press <-Ctrl+Enter->.

To compare dates — variant B:

1. Select cell B2:B11 and type the following formula: =IF(A2>=TODAY(),IF(A2=TODAY(),"Today",
"Future"),"Past").
2. Press <-Ctrl+Enter->.

USE THE IF FUNCTION TO CHECK WHETHER A DATE IS IN THE PAST OR FUTURE USING MICROSOFT EXCEL

Microsoft Excel Tutorials, MS Excel Tutorials, Logical Functions in Excel, IF function in MS Excel, TODAY() Function in Excel, Use the IF and TODAY function to check whether a date is in the past or future using MS Excel, Video Tutorials of Microsoft Excel, Advanced Functions in Excel, Formulas in Excel, Using Macros & VBA Codes in Excel

USE THE IF FUNCTION COMBINED IN ONE CELL MORE THAN SEVEN TIMES USING MICROSOFT EXCEL

The Excel online help says that it is not possible to combine the IF function more than seven times. That is not true, as shown below.

To insert more than seven conditions:

1. Select cell A1 and enter 12.
2. Select cell B1 and type the following formula:

=IF(A2=1,A2,IF(A2=2,A2*2,IF(A2=3,A2*3,IF(A2=4,A2*4,IF(A2=5,A2*5,IF(A2=6,A2*6,IF(A2=7,A2*7,
)))))))+IF(A2=8,A2*8,IF(A2=9,A2*9,IF(A2=10,A2*10,)))+IF(A2=11,A2*11,IF(A2=12,A2*12,)).

3. Press <-Enter->.

USE THE IF FUNCTION COMBINED IN ONE CELL MORE THAN SEVEN TIME USING MICROSOFT EXCEL

Microsoft Excel Tutorials, MS Excel Tutorials, Logical Functions in Excel, Multiple IF Functions, IF Function in Excel, Use the IF function combined in one cell more than seven times using MS Excel, Video Tutorials of Microsoft Excel, Advanced Functions in Excel, Formulas in Excel, Using Macros & VBA Codes in Excel

USE THE TYPE FUNCTION TO CHECK FOR INVALID VALUES USING MICROSOFT EXCEL

Sometimes Excel cannot interpret some values, especially imported data. As an example, let’s say a cell contains an apparent value but the calculation leads to an incorrect result. To prevent this, use the IF function in combination with TYPE to check for invalid data in the worksheet. This example will enter the text “invalid value” in column B if the value entered in column A is not numeric.

To show invalid values in a worksheet:

1. Enter some values or text in column A.
2. Select cells B2:B10 and type the following formula: =IF(AND(TYPE(A2)=1,A2""),A2,"invalid value").
3. Press <-Ctrl+Enter->.

USE THE TYPE FUNCTION TO CHECK FOR INVALID VALUES USING MICROSOFT EXCEL

Microsoft Excel Tutorials, MS Excel Tutorials, Logical Functions in Excel, TYPE function in MS Excel, IF function in Excel, AND Function in Excel, Use the TYPE function to check for invalid values using MS Excel, Video Tutorials of Microsoft Excel, Advanced Functions in Excel, Formulas in Excel, Using Macros & VBA Codes in Excel

USE THE INT FUNCTION WITH THE IF FUNCTION USING MICROSOFT EXCEL

To see if one value is a whole number and can be divided by another value, use the IF function in combination with the INT function.

To see if a whole number can be divided by 4:

1. Select cells B2:B10 and type the following formula:
=IF(INT(A2/4)=A2/4,"whole number divisible by 4",FALSE).
2. Press <-Ctrl+Enter->.

Or

1. Select cells C2:C10 and type the following formula:
=IF(A2/4-INT(A2/4)=0,"whole number divisible by 4", FALSE).
2. Press <-Ctrl+Enter->.

USE THE INT FUNCTION WITH THE IF FUNCTION USING MICROSOFT EXCEL

Microsoft Excel Tutorials, MS Excel Tutorials, Logical Functions in Excel, IF Function in Excel, INT Function in Excel, Use the INT function with the IF function using MS Excel, Video Tutorials of Microsoft Excel, Advanced Functions in Excel, Formulas in Excel, Using Macros & VBA Codes in Excel

USE THE IF FUNCTION TO COMPARE THE TWO CELLS USING MICROSOFT EXCEL

The following tip is a solution for comparing two cells line by line.  Prepare a new worksheet, filling the first two columns with the values 0 and 1 as shown in Figure 2-12.

To compare cells line by line:

1. Select cells C2:C11 and type the following formula:
=IF(A2&B2="11","OK",IF(A2&B2="10","First Value is OK",IF(A2&B2="01","Second Value is OK",”Both Values are FALSE”))).
2. Press <-Ctrl+Enter->.

USE THE IF FUNCTION TO COMPARE THE TWO CELLS USING MICROSOFT EXCEL

Microsoft Excel Tutorials, MS Excel Tutorials, Logical Functions in Excel, IF Function in Excel, AND Function in Excel, Use the IF function to compare the two cells using MS Excel, Video Tutorials of Microsoft Excel, Advanced Functions in Excel, Formulas in Excel, Using Macros & VBA Codes in Excel

USE THE IF FUNCTION TO CALCULATE THE COMMISSIONS FOR INDIVIDUAL SALES USING MICROSOFT EXCEL

A company has a policy for individual commissions depending on sales, as shown below:

Sale < $100                                                3%
Sale => $100 and < $500                           5%
Sale >= $500                                              8%

To calculate the commissions:

1. Enter different possible sales amounts in column A.
2. Select cells B2:B12 and type the following formula: =A2*IF(A2>=500,0.08,IF(A2>=100,0.05,0.03)).
3. Press <-Ctrl+Enter->.

USE THE IF FUNCTION TO CALCULATE THE COMMISSIONS FOR INDIVIDUAL SALES USING MICROSOFT EXCEL

Microsoft Excel Tutorials, MS Excel Tutorials, Logical Functions in Excel, IF Function in Excel, Use the IF function to calculate the commissions for individual sales using MS Excel, Video Tutorials of Microsoft Excel, Advanced Functions in Excel, Formulas in Excel, Using Macros & VBA Codes in Excel

USE THE IF FUNCTION TO CALCULATE WITH DIFFERENT TAX RATES USING MICROSOFT EXCEL

If two or more different tax rates have to be handled, you can use the IF function to calculate each one individually. Simply combine several IF functions, depending on the calculation.

To calculate the price after tax:

1. In column A, enter some prices.
2. In column B, enter different tax percentages (0, 8, or 10 for this example).
3. Select cells C2:C10 and type the following formula: =IF(B2=8,A2/100*8,IF(B2=10,A2/100*10,A2/100*0)).
4. Press <-Ctrl+Enter->.
5. Select cells D2:D10 and type the formula =A2+C2.
6. Press <-Ctrl+Enter->.

USE THE IF FUNCTION TO CALCULATE WITH DIFFERENT TAX RATES USING MICROSOFT EXCEL

Microsoft Excel Tutorials, MS Excel Tutorials, Logical Functions in Excel, IF Function in Excel, Use the IF function to calculate with different tax rates using MS Excel, Video Tutorials of Microsoft Excel, Advanced Functions in Excel, Formulas in Excel, Using Macros & VBA Codes in Excel

USE THE IF FUNCTION TO CHECK CELLS IN WORKSHEETS AND WORKBOOKS USING MICROSOFT EXCEL

To use an IF statement not only in a worksheet but also in a linked worksheet or workbook, start typing part of the formula, for example, “ =IF(,” then navigate to another worksheet or open up a workbook, select the desired cell, and go back to the first worksheet to finish the formula.

To use the IF function to check out cells in another worksheet:

Type =IF(Sheet8!A2="january","wrong month","OK").

To use the IF function to check out cells in another workbook:

Type =IF('C:\Held\Formulas\Files\[Formulas.xls]Sheet35'!$A$1<>1,"wrong","OK").

USE THE IF FUNCTION TO CHECK CELLS IN WORKSHEETS AND WORKBOOKS USING MICROSOFT EXCEL

Microsoft Excel Tutorials, MS Excel Tutorials, Logical Functions in Excel, IF Function in Excel, Use the IF function to check cells in worksheets and workbooks using MS Excel, Video Tutorials of Microsoft Excel, Advanced Functions in Excel, Formulas in Excel, Using Macros & VBA Codes in Excel

USE THE IF FUNCTION TO DETERMINE THE QUARTER OF A YEAR USING MICROSOFT EXCEL

After entering an initial value, Excel can automatically fill worksheet cells with the names of weekdays or months. Open a new worksheet and type the word “January” in cell A2. Then drag the lower-right point of this cell down to A13 to let Excel create a list containing the months of the year. In this example, we want to indicate which months fall into which quarter.

To determine the quarter of a year in which a particular month falls:

1. Select cells B2:B13 and type the following formula: =IF(OR(A2="January",A2="February",A2="March"),"1st quarter",IF(OR(A2="April",A2="May",A2="June"),"2nd quarter",IF(OR(A2="July",A2="August",A2="September"),"3rd quarter","4th quarter"))).
2. Press <-Ctrl+Enter->.

USE THE IF FUNCTION TO DETERMINE THE QUARTER OF A YEAR USING MICROSOFT EXCEL

Microsoft Excel Tutorials, MS Excel Tutorials, Logical Functions in Excel, IF Function in Excel, Use the IF function to determine the quarter of a year using MS Excel, Video Tutorials of Microsoft Excel, Advanced Functions in Excel, Formulas in Excel, Using Macros & VBA Codes in Excel