Data cleaning with Excel functions
In this tutorial, we are going to look at two functions that can make our work in Excel very easy when cleaning up the data.
We are going to use the SUBSTITUTE( ) Function and the VALUE( ) function to clean up our data and perform calculations on the price increment of products.
You can download the Excel starter file to help you to try out this exercise.
You can watch the full tutorial on youtube to see how I went about it.
Watch the Tutorial on Youtube.
THE SUBSTITUTE FUNCTION
The SUBSTITUTE function in Excel is a function that allows you to replace specific text in a text string with new text. The syntax for the function is as follows:
=SUBSTITUTE(text, old_text, new_text, [instance_num])
The text argument is the original text string that you want to perform the substitution on. The old_text argument is the text that you want to replace, and the new_text argument is the text that you want to replace it with.
The optional instance_num argument allows you to specify which instance of old_text you want to replace. If you omit this argument, the function will replace all instances of old_text with new_text.
For example, if you have the text string “I love to eat Mangoes and Pineapples” and you want to replace the word “Mangoes” with the word “Strawberries,” you could use the following formula:
=SUBSTITUTE("I love to eat Mangoes and Pineapples", "Mangoes", "Strawberries")
The result of this formula would be “I love to eat Strawberries and Pineapples.”
THE VALUE FUNCTION
The VALUE function in Excel is a function that converts a text string that represents a number to a number. This can be useful if you have a column of data that is formatted as text, but you want to perform calculations on it.
The syntax for the VALUE function is as follows:
=VALUE(text)
The text argument is the text string that you want to convert to a number. The function will attempt to recognize the number within the text string and return it as a number.
For example, if you have the text string “1,234” in a cell and you want to convert it to a number, you could use the following formula:
=VALUE("1,234")
The result of this formula would be 1234.
Keep in mind that the VALUE function will only work if the text string is a valid number. If the text string contains any characters that are not part of a valid number, the function will return an error.
