easy Excel tricks for data cleaning

August 1, 2018 · 1 mins
#excel  #data cleaning 

Introduction

Any data analyst will spend a significant amount of time cleaning the data they want to analyze. But even in the era of Big Data and data wrangling in Python and R, you will find yourself working in Excel at some point. The following are some simple Excel functions that I’ve found useful when cleaning a dataset.

1. count the number of words in a cell

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1, " ", ""))+1    


2. count the number of each type

=COUNTIF(A:A, "your string")
          or
=COUNTIF(A:A, A4)



3. make sortable and delete every other row

=MOD(ROW(A1),2)


4. find missing numbers in a sequence

=IF(A2-A1=1,"","Missing")


5. remove characters (or replace with a string)

=SUBSTITUTE(A1, "CHARACTERS TO BE REPLACED", "")


6. find the number of characters in a cell

=LEN(A1)