Transcripts for the above video clip:

Substitute

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.