Use the REPLACE function to replace and calculate - Text Functions - Microsoft Excel Tutorials | Microsoft Excel Tutorials | The World of Tutorials

The following worksheet contains an employee’s work hours.

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