Contents
Replacing one text with another within a given text string is a very common situation when working with data in Excel. This can be done with two functions: SUBSTITUTE (SUBSTITUTE) и ZAMENIT (REPLACE). These functions are similar in many respects, but they also have several fundamental differences and pluses and minuses in different situations. Let’s look at the first of them in detail and with examples first.
Its syntax is:
=SUBSTITUTE(Cell; old_text; new_text; entry_number)
where
- Cell – a cell with text where the replacement is made
- old_text – text to be found and replaced
- new_text – text to replace
- entry_number – an optional argument that specifies the occurrence number of the old replacement text
Note that:
- If you do not specify the last argument entry_number, then all occurrences of the old text will be replaced (in cell C1 – both “Masha” are replaced by “Olya”).
- If only a certain occurrence needs to be replaced, then its number is specified in the last argument (in cell C2, only the second “Masha” is replaced by “Olya”).
- This function distinguishes between lowercase and uppercase letters (in cell C3, the replacement did not work, because “masha” is written with a small letter)
Let’s look at a couple of examples of using the function SUBSTITUTE for clarity.
Replacing or removing non-breaking spaces
When uploading data from 1C, copying information from web pages or from Word documents, you often have to deal with a non-breaking space – a special character that is indistinguishable from a regular space, but with a different internal code (160 instead of 32). It cannot be removed by standard means – by replacing it through the dialog box Ctrl+H or a function to remove extra spaces TRIM (TRIM). Our function will help SUBSTITUTE, which can replace a non-breaking space with a regular space or an empty text string, i.e. delete:
Counting the number of words in a cell
If you need to count the number of words in a cell, then you can apply a simple idea: there are more words per unit than spaces (provided that there are no extra spaces). Accordingly, the formula for the calculation will be simple:
If it is assumed that the cell may contain extra spaces, then the formula will be a little more complicated, but the idea is the same.
Extracting the first two words
If you need to pull out only the first two words from the cell (for example, first name from full name), then you can apply the formula:
It has simple logic:
- replace the second space with some unusual character (for example #) with the function SUBSTITUTE (SUBSTITUTE)
- looking for the position of the character # function TO FIND (FIND)
- cut all characters from the beginning of the string to position # with the function LEVSIMV (LEFT)
- Stripping text from extra spaces, non-printing characters, etc.
- How to count the number of words in a cell
- Convert numbers-as-text to full numbers