For Data Cleaning Very Useful Text_Functions
Names TRIM() LEN()
Manoj p Chakerwarti
SHENOY Manoj
Vakhat soLANKI
Anjana m
Rekha Manoj c
Formula_Text =TRIM(B4) =LEN(B4)
Names LEFT() RIGHT()
1234567890
Shenoy Manoj
Vakhat Solanki
Anjana M
Rekha Manoj C
Syntax =LEFT(TRIM(C12),3) =MID(TRIM(C12),8,3)
Names EXACT() FIND()
Salesman
salesman
Salesman
ManojMankindManpower
Anjana Ma
Syntax =EXACT(B20,B21) =IFERROR(FIND("c",B23),"NA")
CONCATENATE() - Limit is upto 255 strings where as & has no limit
EXACT() is Case Sensitive Function
First Blank Second Blank
Manoj Kumar Sharma 6 12
Manoj Kumar Chakerwarti 6 12
`
LEN() Proper() UPPER()
=LEN(C4) =PROPER(C4) =UPPER(D4)
MID() REPT() CONCATENATE()
-
=MID(TRIM(C12),4,4) =REPT("*",15) =CONCATENATE(D12,"-",F12)
SEARCH() SUBSTITUTE()
Salesman =LEFT(I19,5)
salesman =RIGHT(I19,1)
Salesman =MID(I19,6,4)
ManojpersonkindManpower
Anjana Ma
#N/A =SUBSTITUTE(C20,"Man","person",2)
Midname
Kumar =MID(C38,D38+1,E38-D38-1)
Kumar
Number Formatting -Currency, Dat
lower() Number Number with Spaces No Decimals
1000 0000 1000 1000
1001 0000 1001 1001
1002 0000 1002 1002
1003 0000 1003 1003
1004 0000 1004 1004
=LOWER(D4) Syntax =TEXT(K8,"0000 0000") =TEXT(K8,"0")
& Date of Birth Formatted Date With TEXT() Syntax
- 12/8/1963 December 8, 1963 =TEXT(K12,"mmmm d, yyy
D08 =TEXT(K12,"mmmmmdd")
Dec 8, 1963 =TEXT(K12,"mmm d, yyyy"
Sunday =TEXT(K12,"dddd")
Sunday 08 December-1963 =TEXT(K12,"dddd dd mmm
=D12&" - "&F12
ABGPC3639N
ABGPC AB =MID(I19,1,2)
N 9N =MID(I19,9,2)
3639 GPC=MID(I19,3,6)
Manoj 1 =I24=I25
manoj - =EXACT(I24,I25)
tting -Currency, Date, IS Functions
With Decimals With Comma With Currency
1000.00 1,000 $ 1,000.00
1001.00 1,001 $1,001.00
1002.00 1,002 $1,002.00
1003.00 1,003 $1,003.00
1004.00 1,004 $1,004.00
=TEXT(K8,"0.00") =TEXT(K8,"#,##0") =TEXT(K8,"$#,##0.00")
yntax ISNUMBER() ISTEXT() ISBLANK()
EXT(K12,"mmmm d, yyyy") 1 1 FALSE
EXT(K12,"mmmmmdd") 0 1 FALSE
EXT(K12,"mmm d, yyyy") 0 1 FALSE
EXT(K12,"dddd") 0 1 FALSE
EXT(K12,"dddd dd mmmm-yyy=ISNUMBER(K15) =ISTEXT(L15) =ISBLANK(L15)
Date Functions
Function Date Result
TODAY() 3/21/2025
NOW() 3/21/2025 10:10
DAY() 12/8/1963 8
MONTH() 12/8/1963 12
YEAR() 12/8/1963 1963
DATE() 12/8/1963
Add/Subtract Days using DATE()
DATE() 9/13/2022
DATE()+days 9/23/2022 10
DATE()-days 9/9/2022 4
Add/Subtract Month using EDATE()
Date 9/1/2023 2/1/2024
Date+Months 3/1/2024 6
Date-Months 11/1/2022 -10
Calculate Last Day of the Month EOMONTH()
Date 9/13/2022
Last Day of the Month 2/28/2023 5
Date+Months 11/13/2022 Verified
Manoj 12/8/1963
Manoj 12/8/1963
Formula Date-of-Birth Today's Date
=TODAY() 12/8/1963 3/21/2025
=NOW()
=DAY(D6) Total-Years 61
=MONTH(D7) Total-Months 735
=YEAR(D8) Total-Days 22384
=DATE(YEAR(D6),MONTH(D6),DAY(D6))
Years 61
Formula Months 3
Days 13
=D12+E13 61 years 3months 13 days.
=D12-E14 Ctrl+; 1/19/2021
Formula Start-Date 4/1/2023
End-Date 3/31/2024
=EDATE(D17,E18)
=EDATE(D17,E19) NetWorkDays 260
NetWorkDays 259
Formula NetWorkDays.intl 311
=EOMONTH(D22,E23) Date Vs Days (dddd)
Holidays Display
d 4/1/2023 Saturday
12/26/2023 Tuesday
3/21/2025 Mar
21
2025
Confusion - Which is DD and MM
6/3/2023 3
06/03/2023 3
Current Date Ctrl+; 2/18/2024
Current Time Ctrl+Shift+; 22:43
Formula
=TODAY()
=DATEDIF(H4,I4,"y")
=DATEDIF(H4,I4,"M")
=DATEDIF(H4,I4,"d")
=DATEDIF($H$4,$I$4,"Y")
=DATEDIF(H4,I4,"ym")
=DATEDIF(H4,I4,"md")
61 years 3months 13 days.
Formula
=NETWORKDAYS(I16,I17)
=NETWORKDAYS(I16,I17,H25:H26)
=NETWORKDAYS.INTL(I16,I17,11,H25:H26)
March / 21, 2025 (Friday)
Computer Calendar Starts from 1900
Number 1 is the first date of the computer Calendar
12/31/1899
Use of COUNT(), COUNTA(), COUNTBLANK(), COUNTIF() and COUNTIFS()
Function Remarks Seminar Seat Bookings
COUNT() Counts where numeric data is available F F F F
COUNTA() Counts the filled cells M M M 1
COUNTBLANK() Counts only blank cells F F F 1
COUNTIF() Counts on the basis of Criteria selected M M M 0
COUNTIFS() Counts for multiple criteria F F F 1
F F F F
F F F F
F F F F
M M F
M M F
L E A R
E A R
NTIFS()
okings Count EmpName DoJ
F F F F Numbers 8 Name-01 6/15/2022
0 M M M Booked 75 Name-02 1/1/2023
0 F M M Pending 5 Name-03 2/4/2017
1 M M M Male 28 Name-04 2/5/2022
1 M M Female 39 Name-05 6/15/2024
F M M F Name-06 4/1/2021
F M M F Practice Count Name-01 8/2/2021
F F F F Numbers Name-08 2/10/2022
F M M M Booked Name-09 3/11/2022
F M Pending Name-10 3/1/2023
Male
Female
N
N
Count Number of Employees Within The Specific
Criteria
From Date 6/15/2020
To Date 6/15/2022
Count = 6
=COUNTIFS(S4:S13,">="&V5,S4:S13,"<="&V6) Preet
Shah
COUNTIFS() is used for multiple Criteria
Conditional Formatting is used Preet Shah
Concatenation is used and Logical operators
are also used
=COUNTIFS(S4:S13,">="&V5,S4:S13,"<="&V6)
Statistical Functions
Location Rate Function Result
Store-1 10.00 MIN 6.00
Store-2 15.00 MAX 45.00
Store-2 6.00 SMALL 10.00
Store-1 1.00 LARGE 40.00
Store-2 40.00
Store-3 25.00
Store-1 35.00
Store-1 45.00
Data Enter K 5
6.00
10.00
15.00 Small-5 25.00
20.00 Min 6.00
25.00 Max 45.00
30.00 Large-5 30.00
35.00
35.00
40.00
45.00
Formula
=MIN(D4:D11)
=MAX(D4:D11)
=SMALL(D4:D11,2)
=LARGE(D4:D11,2)
=SMALL(D14:D23,$G$13)
=MIN(D14:D23)
=MAX(D14:D23)
=LARGE(D14:D23,$G$13)
