NITA/TRN/2073

IHRMâ–ºC00453

## How to Clean Any Special Character From a String

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...

## 3 Ways to Count Partial Matches in a String

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)...

## How to Count Uppercase Lowercase or Numbers in a String

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})…...

## How to List Missing Numbers In a Sequence

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...

## How To Create a Dynamic Waterfall Chart in Excel

Waterfall charts are used to explain the +ve and -ve components of a change in value between two points. It reveals the contribution per factor affecting the item being analyzed. For example, If you had Budgeted \$ 66M but ended up spending \$ 72M, you can easily tell...

## How To SUMIF Excluding Weekends & Holidays

Summing data based on weekdays or weekends is easy, but what if you are required to SUMIF Excluding Weekends & Holidays? There are 2 ways of doing this: Using SUM, IF & WORKDAY FUNCTION =SUM(IF ( WORKDAY(tbls[Sales Date]-1,1,Holidays) = tbls[Sales...