How to substitute a value in Microsoft Excel

How to substitute a value in Microsoft Excel. Substituting values ​​in Microsoft Excel means replacing one part of the text with another and displaying the result in a separate cell. This will save you from having to make the same adjustments to a large number of fields yourself. A similar manipulation is carried out using just one function, but later I will also talk about its modernization for those who correct erroneous imports by converting text values ​​to numeric ones.

When might it be needed?

I will answer the most important question on the topic: “When might you need to substitute values?”. Performing this operation in the first place involves replacing any characters. For example, you imported a range of values ​​from another program that uses a dot instead of a comma to separate the fractional part. Accordingly, Excel will think that these are text values ​​and will refuse to use them when creating functions.

You can fix all this manually by removing old characters and adding new ones, and then check the format settings of each cell. However, this is long and inconvenient. It’s easier to create a new column using functions. SUBSTITUTE and VALUE . This will create a column with the correct data that can be used for any purpose, including copying and deleting everything unnecessary.

Using the SUBSTITUTE function

First, let’s figure out how the syntax of the main function works – SUBSTITUTE. We will declare it and consider each argument so that you will not have any problems with further use.

1. First, select the required cell, click on it with the left mouse button to activate it and declare the function =SUBSTITUTE() .
2. In parentheses, write the number of the cell from which you want to take the original text.
3. Put a semicolon to separate the arguments, in quotes write the character or characters you want to get rid of.
4. Separate the argument again and write the new character in quotes to replace it.
5. This is the whole formula, so you can press Enter and see what happens in the end.

For clarity, I leave the formula I use in its entirety so that you can edit it for yourself and not enter each character manually. This will help save a lot of time.

=SUBSTITUTE(A2;".";",")

Adding the VALUE function

Now let’s modernize the formula to end up with a numeric value, not text, as in the original version. To do this, you need the VALUE function, which you just need to write into the string with extra quotes.

1. Edit to end up with something like this: =VALUE(SUBSTITUTE(A2;”.”;”,”)) .
2. Press Enter to apply the changes and notice that the numbers now appear on the right side of the cell, indicating that the format change from text to number was successful.
3. It remains only to stretch the formula to all other cells in order to complete the replacement in the data array. Pinch the lower right corner of the cell and drag down.
4. Look at the table and remove redundant information that is no longer needed after the conversion.

Working with data arrays in Microsoft Excel often forces the user to make changes to the text, which is especially true when importing fractional numbers. Now you have learned that you can solve the problem with just one formula, consisting of two functions. Use this to optimize your spreadsheet interaction.