How to substitute characters in excel using the SUBSTITUTE function
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.
If I understand the query below correctly, the one idea is to type some words in a cell, and then, while holding the ALT key down, click ENTER. Within the cell you will get another line. Hope this helps. Original query Hi, … Continue reading
A recent email query asked how to share and unshare workbooks in Microsoft Excel with a specific focus on switching off the sharing. The method to share and ‘unshare’ the workbook is the same. As shown in the image below, … Continue reading
“Data accuracy and consistency has improved dramatically across Absa Capital, so much so that it has become compulsory within certain areas for all staff to complete Adrian’s Data Analyses Spreadsheet Techniques course.”– Absa Capital