find and replace function in excel for mac

Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs.

Replace or Add Characters to Data with Excels REPLACE Function.

Use Excels REPLACE function to replace unwanted text data in a worksheet cell with good data or with nothing at all.

Imported or copied data sometimes includes unwanted characters or words along with good data.

The REPLACE function is one way to quickly correct this situation as shown in the example in the image above.

This is especially true when long columns of imported data need correcting since it is possible to use the fill handle or copy and paste to copy the REPLACE function to multiple cells in the worksheet.

The types of text data that the function can replace includes.

A functions syntax refers to the layout of the function and includes the functions name, brackets, and arguments.

Startnum specifies the start position - from the left - of the characters in Oldtext to be replaced.

Numchars specifies the number of characters to be replaced after Startnum.

If blank, the function assumes that no characters are to be replaced and adds the characters specified in the Newtext argument - row three above.

If blank, the function assumes that no characters are to be added and just removes the characters specified for the Numchars argument - row four above.

VALUE occurs if the Startnum or Numchars arguments are negative or contain non-numeric values, row eight above.

When using the REPLACE function with numbers as outlined in the steps below, the formula results (24,398) are treated as text data by Excel and may return incorrect results if used in calculations.

Identical to the REPLACE function in purpose and syntax is REPLACEB.

According to Excels help file, the only difference between the two is the group of languages that each is intended to support.

This example covers the steps used to enter the REPLACE function into cell C5 in the image to replace the first three characters of the text string 24,398 with a dollar sign () to get 24,398.

Options for entering the REPLACE function include manually typing in the entire formula.

Although possible to manually enter the function and its arguments, it is often easier to use the dialog box as it takes care of the functions syntax - such as brackets and comma separators between arguments.

REPLACE and Excels other text functions are designed to leave the original data in one cell with the edited text placed in another.

Doing so keeps the original data intact for future use or makes it possible to correct any problems that occur during editing.

At times, however, it may be preferable to remove the original data and just keep the edited version.

To do this, combine the REPLACE functions output with paste value - which is part of Excels paste special feature.

Find And Replace Function In Excel For Mac