Substitute Characters in excel

How to substitute characters in excel using the SUBSTITUTE function

• 01:54
• Skill Level: 5
• What's my skill level
  

  All of our videos are listed by user Excel Skill Level.

  To find out your personal skill level, download our quick assessment.

  Once you've completed the test, send it to us and we'll evaluate for you.

  

• 

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.