#14 Cleaning Txt
date
May 18, 2023
slug
14-cleantxt
status
Published
tags
summary
Text Cleaning and Transformations
time
0h-50m
type
Post
Learned about ways to clean the text in the spreadsheets and text is more prone to errors. It was a short and interesting lesson, loved it.
Time Difference
TODAY()&NOW()returns based on the time zone settings.- It doesn’t take any arguments
- Subtracting dates returns the interval in days.
DATEDIF(start_date,end_date,unit)(date-dif not dated-if) returns the time passed between two dates- The unit can be
Y,M,D - Results are chopped to nearest unit rather than being rounded
Cleaning Up Text
UPPER(),LOWER(),PROPER()converts to respective cases.
TRIM()will remove all extra whitespaces- Leading : before text
- Trailing : after text
- Repeated : >1 space b/w text
CONCATENATE(string1,[string2,...])will join all these without any character between.CONCAT()only takes two arguments.
LEN("hi")returns length of string,2in this case
`SEARCH(search_for, text_to_search, [starting_at])returns the index
LEFT(string,[no_of_chars])&RIGHT(string,[no_of_chars])will extract characters from each side
SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_no])will replace text with other text