by Crispo Mwangi | Aug 5, 2022 | ADVANCED EXCEL
The ability to clean data is one of the most valuable skills for any data analyst. Below are 3 ways how to clean any special character from a string. Using a Formula...
by Crispo Mwangi | Jul 29, 2022 | ADVANCED EXCEL
Recently a friend approached me with the below issue. How do you compare if the applicant has all the 3 required qualifications? Using a formula =IF(SUM(ISNUMBER(SEARCH($G$3:$G$5,C3))+0)=3,”Y”,”N”) How the formula works: SEARCH($G$3:$G$5,C3)...
by Crispo Mwangi | Jul 22, 2022 | ADVANCED EXCEL
Here are 3 methods you can use to count uppercase lowercase or numbers in a string. Using a Formula =SUM(ISERROR(MID(B3,SEQUENCE(LEN(B3)),1)+0)*EXACT(LOWER(MID(B3,SEQUENCE(LEN(B3)),1)),MID(B3,SEQUENCE(LEN(B3)),1))) =SUM({0;0;0;1;1;1;0;0}*{1;1;1;0;1;1;1;1})…...
by Crispo Mwangi | Jul 15, 2022 | INTERMEDIATE EXCEL
Extracting numbers from a string in excel is a must-have skill in data cleaning. There are different methods of this extraction depending on the position of the numbers. Extracting numbers at the end of a string Scenario 1: There is a separator between text and...
by Crispo Mwangi | Jul 8, 2022 | ADVANCED EXCEL
Missing numbers in a sequence can lead to wrong results in data analysis, especially if you are using these numbers to keep a count of items. In the last article, we showed How to Highlight Non-Sequential Numbers. This article shows how to list the missing numbers...
Recent Comments