sarbanes oxley spreadsheet control measurements are discussed on this website and page
excel spreadsheets
auditing in South Africa Audit Excel
 

 

Thank you for visiting our site. Please tell everyone you know about us.

Courtesy of www.AuditExcel.co.za

 

Below is the requested video clip. Other pages you may want to visit on this site:

AuditExcel.com- More training material

Training - Our training index for all things spreadsheet.

Home Page - See everything we offer

or use the search bar to the right to find exactly what you want.

Email us on info@AuditExcel.co.za if you have any questions

 

Web

AuditExcel.co.za

 
  Video loading. While you wait, see what other videos are available on the left hand side

 

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.

 

 

Home | Contact Us | Sitemap | Training | Sarbanes Oxley | Articles | Links | Blog