Excel REPLACE, SUBSTITUTE functions (With Examples)

No need to change or retype your data set as you can easily change the data using replace function. It will not only make easier to replace the data but it also saves lots of time. Here you can check how to apply excel replace function to replace the string or text with the old string or text.

Excel Replace Function

The Replace function in Excel is used to replace a part of the string with different text string depending on the number of specified characters. After using this replace function, it will give you the text string which is replaced by the specified string.

Excel Replace

Suggested Read: Excel Compare Two Columns

Syntax of Excel Replace Function

=Replace(previous_text, start_num, num_chars, new_text) where

  • Previous_text is the text that you want to replace with a new string.
  • Start_num is the position from where the finding begins.
  • Num_chars is the total number of characters that you want to replace.
  • New_text is the text that you actually want.

NOTE: The start_num and num_chars must be positive.

1.      How to use Excel Replace (Shortcut)?

  • Select the cell where you want the actual or replaced text.
  • After clicking on the cell, press Ctrl+H shortcut key which will open the Find and Replace dialog box.

Find and Replace function

  • In Find What text bar, you need to write the text that you want to search for.
  • In Replace With text bar, write the value that replaces the old value.
  • If you want to replace the occurrences one by one then click replace icon whereas if you want to replace all the entries then click Replace all icon.

Replace the string with No value

To replace any of the specific value with no value, then type the text that you want to search in the Find what text bar where leave the replace with text bar blank with no value. Now, click on replace all icon.

2.      Find and Replace in Excel via Formatting

  • You can use Find and Replace icon placed on the toolbar of the excel sheet.
  • Choose the cell that you want to replace.

Excel Replace func

  • Go to Home and then Find and Select icon.
  • Click on Replace icon.
  • You will find the options button below the dialog box.

Excel replace formatting

  • Click on the drop-down Format icon.
  • You will then find Choose Format from cell option.
  • You will find preview mentioned in the text bar.

 

Replace in excel

  • Click on Replace All icon.
  • This will pick up and replace the background color, borders, fonts, and merged cells.

Excel find and replace function

3.      Find or Replace Line Break in Excel

  • In order to replace the line break with a separator, you need to enter the line break character in the text bar of Find What by pressing Ctrl+J shortcut key. You will find tiny flickering dot which is the line break character.

Excel replace with line break

  • In the replace with text bar, enter the space character which is the replacement character.
  • Click on the Replace All icon.

4.      How to replace text in Excel (Wildcard Characters)

  • Choose the data.
  • Look for the Find and Select icon at the toolbar.
  • Click on replace the icon.
  • In Find What, mention (*)     **The asterisk is a wildcard character representing the number of characters.

Replace function

  • In Replace With, Leave blank.

Also Read

Leave a Reply

Your email address will not be published. Required fields are marked *