How to substitute characters in excel using the SUBSTITUTE function.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
Substitute Characters in excel
The Substitute function allows you to substitute certain parts of text within a cell with other text.
So, for example, we have information in column B cells 4 to 8 (as shown). Even though these look like numbers, Excel is seeing it as text. So if for example I said B4 + 1 Excel gives an error message. You could try the VALUE function which you can learn about elsewhere, but in this case it is not working and that is because Excel sees the comma in 1,000 as text it has turned the whole thing into text. What we would like to do is replace the comma with nothing. So go to the function wizard and find SUBSTITUTE – click OK. A window will open. First thing to insert is “Text” – put B4; “Old_Text” is the existing text which you want to replace – insert “,” ; “New_Text is blank so I insert “” (two inverted commas next to each other)– you may want to replace it with words or other characters; there is also “Instance_num” which allows you to replace the comma only the first time you find it. If you leave it blank it replaces the comma every time it finds it. Click OK. The result you will see is that you have taken the text in cells B4 to B8 and replaced the commas with nothing in cells F4 to F8 and Excel is now reading this column as numbers and not text. You can now work further with this.