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...
by Crispo Mwangi | Jul 1, 2022 | INTERMEDIATE EXCEL
Data validation is very important as it prevents bad data entry and thus saves time in data cleaning. Let us see how to prevent duplicates in data entry. There 4 steps to building a good data validation: Test your validation formula firstSelect the area to apply the...
Recent Comments