Working with Downloaded Data – Manipulating Text and Numbers

Course Description:

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.
 

Learning Objectives:

Upon successful completion of this course, participants will be able to:

• 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