How to quickly replace text in Excel

When we type text into Excel cells, it can happen that we make mistakes and find ourselves with many words to correct in one go. To remedy these errors we can make use of the function Replace, integrated into all versions of Excel supported by Microsoft and usable both in an empty cell and in a specific cell. Alternatively we will also show you how to use the function Find and replacea variant of the function also present in Word.

READ ALSO -> Excel tricks to become experts in calculations and data presentation

1) How to use the Replace function

To use the function Replace of Excel we can use two different methods:

• write = (equals) in the field in any cell and type REPLACE;
• press at the top of the menu Formulaclick on Text and finally press on REPLACE.

After opening the function, we can start using it by analyzing the various entries that can be added to a common string:

=SOSTIUISCI(testo;"testo_prec";"nuovo_testo";[occorrenza]

After the round bracket we can insert the coordinates of the cell to replace, followed by the previous text and the following text; to make the substitution more effective (for example if we wanted to change just one letter) we can also use the necessary ones, so as to specify the position of the letter to be modified.

Examples of the Replace function

The Replace function can be tested in a cell with some ready-made text and use it as a reference. If for example we already have a cell with written I arrive and we want to generate a cell or cells with text Activewe can use the following string:

=SOSTITUISCI(Numero-cella;"Arrivo";Attivo")

Make sure to select the reference cell and insert the text inside the quotes. If we want change the text to all cells that use the same reference word just use a string similar to the one visible below:

=SOSTITUISCI("Testo-prova";"-";" ")

With this we can remove the hyphen present in the center of the word and immediately generate a space between them. If, however, we wanted to replace only one of the letters present in the sentence we can use the occurrence to decide precisely what to replace, as in the example:

=SOSTITUISCI(Cella;"a";"i";2)

With a command like this we will use the reference cell for the text to be replaced and we will change the second a found inside the cell with an i (for example from Enamel a Dispose); by copying and pasting this formula we can also apply it to other similar cells, adapting it based on the context.

2) How to use Find and Replace

A good alternative method to quickly replace text in Excel involves using the function Find and replacevery similar to the one present on Word.

To start it, go to the top of the menu Homeclick at the top right on Find and select then we press on the menu Replace. In the new window we can enter the text to search for in the Find field and the text to replace in the field Replace withusing the button Find everyone to highlight all the corresponding texts, the keys Previous e Following to navigate between the highlighted items, the Replace button to replace one item at a time and finally the button Replace everything to immediately apply the change to all the corresponding text.

Conclusions

Excel’s Replace function is really very convenient for immediately generate correct text in a new set of cellsgiven that it can be extended in rows and columns with extreme ease (even moving the mouse in the cells while writing the formula).

In addition to the function Replace we can also use Find and replacemore convenient to use to quickly replace the incorrect text in our spreadsheet.

To learn more we can read our guides on how to search and replace text and words on many files or documents at once come on how to find and replace words in Word and Google Docs.