7db49c3c99eb854592129ad1267712f5 Week 2 Data cleansing values Handout

Prévia do material em texto

CLEAN function
The CLEAN function removes all nonprintable characters from text
The CLEAN function looks at a value in one cell, and removes nonprintable characters
Can be nested with other data cleansing formulas
(I.e. Trim, Value)
Cleaning large data sets
Removing extra characters that are unwanted in specific cells or a range of cells
Overview
Syntax
Tips and Tricks
Uses and Applications
=CLEAN(Text)
=CLEAN(Value you want specific character removed from)
Function
Input Criteria
1
TRIM function
The TRIM function removes all spaces from a string (with the exception of single spaces between words)
Can be nested with other data cleansing formulas
(i.e. Clean, Value)
Use TRIM on text that you have received from another application that may have irregular spacing
Overview
Syntax
Tips and Tricks
Uses and Applications
=TRIM(Text)
=TRIM(Value you want the spaces removed from)
Function
Input Criteria
2
SUBSTITUTE function
The SUBSTITUTE function substitutes new_text for old_text in a text string
Use quotes around text strings
Use SUBSTITUTE when you want to replace specific text in a text string
Overview
Syntax
Tips and Tricks
Uses and Applications
=SUBSTITUTE(Text, Old_Text, New_Text, Instance_num)
=SUBSTITUTE(Text or reference to text you want to substitute characters, text you want to replace, the text you want to replace old text with, (Optional) Specifies with instance of old text you want to replace with new text)
Function
Input Criteria
3
VALUE function
Generally you do not need to use the VALUE function in a formula because Excel automatically converts text to numbers as necessary
Text can be in any of the constant number, date, or time formats recognized by Microsoft Excel
Overview
Syntax
Tips and Tricks
Uses and Applications
=VALUE(Text)
=VALUE(Cell to be converted)
Function
Input Criteria
The VALUE function converts a text string that represents a number into a number
The text enclosed in quotation marks or a reference to a cell containing the text you want to convert
4
TEXT function
The format text argument is not case sensitive
Combine a set of data to a useful sentence
Write today’s date as a sentence
="Today's date is "&TEXT(TODAY(),"mm/dd/yy") would return “Today’s date is 08/1/16”
Overview
Syntax
Tips and Tricks
Uses and Applications
=TEXT(Value, Fomat_Text)
=TEXT(Value you want to format, “Format you want to apply to that value”)
Function
Input Criteria
The TEXT enclosed in quotation marks or a reference to a cell containing the text you want to convert
5
Convert Text to Columns Wizard
Use the data preview box to check the output of your data
Be sure to select the appropriate destination for the data you wish to alter
Split content based on a delimiter
Split cell content based on a column break
Overview
Syntax
Tips and Tricks
Uses and Applications
The Convert Text to Columns Wizard can be used to separate simple cell content, such as first names and last names, into different columns
This function can delimit data based on any symbol or character such as a dash or underscore
To access the wizard, select “Data” on the ribbon menu and select “Text to Columns”
6