Fresh Excel Tips

Here, we will be learning how to use the REPLACE function to replace characters in Excel. For this example, we realize that our misinformed assistant has entered a list of times in Columns A using a period rather than a colon, example, 7.30 instead of 7:30. Here we will use the REPLACE function along with the SEARCH Function to correct this mistake. Here is the syntax used for the REPLACE Function:

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.

How to USE the REPLACE Function in Excel

1. Open a new worksheet and enter your data into Column A
2. Select cells B1:B10 and type the following formula: =(REPLACE(A1,SEARCH(”.”,A1),1,”:”).
3. Press Ctrl+Enter.

This will replace the first instance of a period with a colon. You should now be set. This will work in Excel 97, 2000, 2003 and 2007.

1. Using the SUBSTITUTE function to substitute characters in Excel
2. Separate First name from Last name using SEARCH in Excel
3. Using the SUBSTITUTE function to substitute parts of a cell
4. How to Use the CLEAN function to remove nonprintable characters
5. How to Use a custom format to create a time format