Working with Downloaded Data - Manipulating Text and Numbers (Course Id 2809)

• Who is this course for?
  This course is designed for CPAs and other professionals seeking Continuing Professional Education who use Microsoft Excel and need to work with downloaded data in text format.
• What is this course about or what problem does this course solve?
  This course focuses on teaching how to manipulate and convert text-formatted data in Excel, including numbers and dates, into usable formats using tools like Text to Columns and various Excel text functions.
• How can the knowledge from this course be used?
  The knowledge can be applied to clean, transform, and organize raw downloaded data in Excel, making it more accurate, functional, and suitable for analysis and reporting.
• Why is this course important to a CPA or Accountant?
  This course is important because CPAs and accountants frequently work with imported financial data that requires formatting and cleanup, and mastering these Excel techniques improves efficiency and data accuracy.
• When is this course relevant or timely?
  This course is relevant whenever professionals are working with downloaded or imported data in Excel, especially when dealing with inconsistencies in text-formatted numbers or dates.
• How is a course like this consumed or used?
  This is a self-study text-based course that includes step-by-step instruction, review questions, an exercise file, and a final exam that must be passed to earn CPE credit.

This course covers the Excel tools and techniques needed for dealing with data that is in a text format when downloaded. It also discusses how to manipulate text and numeric data and ways to present it in a more user and Excel friendly manner. If you have ever downloaded a number such as $34.00- or $34.00CR and needed to convert it to a number then this is the course for you. 

This course includes discussions on: 

• Converting numbers and dates formatted as text to numbers using Text to Columns and Excel text functions such as TEXTSPLIT(). 
• Extracting specific characters from text or a number using functions such as MID() and TEXTSPLIT().
• Concatenating text and numbers together using CONCAT() or TEXTJOIN()
• Cleaning data using TRIM() and CLEAN()
• Changing case of text with PROPER() and UPPER() and LOWER()
• Counting characters in a string using LEN()
• Using other text functions such as DATEVALUE(), TIMEVALUE, FIXED and REPLACE
• Functions covered include: LEFT, RIGHT, MID,LEN, EXACT,FIND, FIXED, REPLACE, SUBSTITUTE, CONCATENATE, CONCAT, TEXTJOIN,TEXTBEFORE,TEXTAFTER, TRIM, CLEAN, UPPER, LOWER, PROPER, SUBSTITUTE, NUMBERVALUE, TIMEVALUE,DATEVALUE.

This course provides step by step instruction. It also provides an exercise file. In some cases, an answer sheet is also provided in the file.

NASBA QAS - Text - NASBA Registry - 2809

Course Learning Objectives

• Recognize the methods used to convert numbers formatted as text to numeric values and dates formatted as text to date values recognized by Excel
• Recognize functions used to split or extract text from a string
• Identify process of joining text together and recognizing different concatenation functions
• Recognize the uses of Text to Columns
• Identify how to evaluate text using functions such as LEN()
• Recognize functions available to clean and format text

Chapter 1 - Working with Downloaded Data - Manipulating Text and Numbers

Course Learning Objectives:

Overview

Numbers Formatted as Text

Ways to Convert Text to a Value -Text to Columns

Ways to Convert Text to a Value: =VALUE()

Ways to Convert Text to a Value – Trace Error

Ways to Convert Text to a Value – Paste Special

Text to Columns

TEXTSPLIT()

Commonly Used Text Functions to Extract Characters

LEFT()

MID() Let’s display the middle 12 characters of the contents in cell A1.

RIGHT()

TEXTBEFORE() and TEXTAFTER()

Concatenate Functions

CONCATENATE() and CONCAT()

AMPERSAND

TEXTJOIN()

Concatenation and Dates

Combining Text Functions and an IF Function

Review Questions

Evaluating Text using LEN

Other Useful Text Functions

UPPER()

PROPER()

EXACT()

FIND()

REPLACE()

SUBSTITUTE()

TRIM()

CLEAN()

DOLLAR() and FIXED()

FIXED()

Working with Dates

Text to Columns

DATE VALUE()

TIME VALUE()

Summary

Review Questions

Link for Spreadsheet

Glossary