Find and Replace in Excel

If we are importing data into Excel, particularly from text files, we sometimes need to edit our imported text. Luckily there are some surprisingly easy ways to do that, using formulas. We have already looked at the trim function (here). This time we look at find and replace.

If, for example, our text strings contain some of the same characters or patterns, we can use Excel's find and replace function. We can replace everything following the colon in Extend Office's screen dump below by using a wildcard character (14 July 2014). All we need to do to replace our chosen data is to:

1. Select (highlight) the cell range we want to replace the data within, and
   
2. Key Ctrl and F to bring up the find and replace dialogue box, and
   
3. Enter the character you want the find and replace function to find (use an asterisk for "everything past this character", or "everything before this character") in the "Find what" box, and
4. Enter the text string that you want to replace the existing text with (or enter nothing, if you simply want to delete the text altogether) into the "Replace with" box.
5. Click "Replace All" (or Ctrl & A) to amend your data:
   
   
6. If you decide this was all a catastrophic fail, simply click the Undo icon.

Like the trim formula, this is also easy to amend. Have a play: it too makes editing easy. Thanks, Extend Office, for sharing this!


Sam

• Reference: Extend Office (14 July 2014). How to remove first, last or certain characters from text in Excel? Retrieved 2 July 2017 from https://www.extendoffice.com/documents/excel/560-excel-remove-character-from-string.html