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 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 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 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 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 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 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 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