Advanced Excel Session By:
Pankaj Kumar Gupta
Website: [Link]
[Link]
Email: advancedexcel07@[Link]
info@[Link]
Phone: 9871076667, 8750676667
Facebook Page: Like Our Facebook Page.
Linkedin Page: Connect With Me On Linkedin
By:
t
om
[Link]
t
e.
Linkedin
Text Functions. TEXT/CELL REF
Concatenate with
Name Quality Concatenate Symbol/Space
Jay Is a Manager
Harpreet Is a [Link]
Gaurav Is a Executive
Data Left Mid
Jay-Is a good boy
Harpreet-Is a good boy
Gaurav-Is a good boy
Jayprakashanan-is a good boy
Data New Text Replace
Jay Is a Manager Neeraj
Harpreet Is a [Link] Ravi
Gaurav Is a Executive Jaynarayan
Data Trim
Kamal Is a Manager
Neeraj Is a [Link]
Vishal Is a Executive
Data Left Mid
Jay Is a good boy
Harpreet Is a good boy
Gaurav Is a good boy
Jayprakashanan is a good boy
Upper Lower Proper
Right Find Len
Text Substitute
Ram is a good good good boy
Question 1 : Joint all the text in F4: F6 with space in between Answer
Peter is a good boy
Robin wants to drive car
Harsh likes to play football
Question 2 : Separate the First and second name in different cells
Text First Name Second Name
Kunal Adhikari
Rakesh Sharma
Gaurav Arora
Nikhil Gupta
Question 3 : Convert the below metion text in proper as per normal english standard
Text Proper
Peter Is a GOOD Boy
Robin Wants TO DRIVE CAR
harsh likes to play football
Question 4 : Separate ISD code and phone numbers
Phone ISD CODE PHONE NUMBER
+91-9871589658
+91-8756529860
+410-5698536
(516)-8965742
+(212)-65789525
Question 5 : By using Substitute Formula replace all the - with space & Convert in proper as per English Std.
Text Answer
Peter-Is-a-GOOD-Boy
Robin-Wants-TO-DRIVE-CAR
harsh-likes-to-play-football
Like: Peter is a good boy
Date Functions.
Data
Day Month Year Date Datevalue Day
20 1 2001
21 2 2002
22 3 2003
23 4 2004
24 5 2005
25 6 2006
Now Hour Minute
Month Year Days360
Second Time Today
Data Format and Data Types
Data Types Data Format
Number Text 2001-A
1000 AAAA 2002-A
2000 BBBB 2003-A
3000 CCCC 2004-A
4000 4000 2005-A
Date Types
20-Jan-14
21-Feb-14
22-Jan-14
23-Mar-14
24-Jan-14
Value Can We Pull a Vlookup from Column F to G
100 2001
200 2002
300 2003
400 2004
500 2005
A B C D E F G H
3
4 Protection of Workbook, Worksheet, Rows, Columns & Ce
5
6
7 Data Vlookup
Pankaj Gupta:
8 2001-A 100 2001 100 You can pull a
9 2002-A 200 2002 200 vlookup.
10 2003-A 300 2003 300
11 2004-A 400 2004 400 Pankaj Gupta:
12 2005-A 500 2005 500 You can pull a
vlookup.
13
14
15 Data Vlookup
16 2001-A 100 2001 #N/A
17 2002-A 200 2002 #N/A
18 2003-A 300 2003 #N/A Pankaj Gupta:
You can't Pull a
19 2004-A 400 2004 #N/A vlookup
20 2005-A 500 2005 #N/A
Pankaj Gupta:
21
You can't Pull a
22 vlookup
23
I J K
3
t, Rows,4Columns & Cells.
5
6
7
Pankaj Gupta:
8
You can pull a
vlookup. 9
10
ankaj Gupta: 11
ou can pull a 12
lookup.
13
14
15
16
17
ankaj Gupta: 18
ou can't Pull a
lookup 19
20
Pankaj Gupta:
21
You can't Pull a
vlookup 22
23
F8: Pankaj Gupta:
You can pull a vlookup.
F10: Pankaj Gupta:
You can pull a vlookup.
F16: Pankaj Gupta:
You can't Pull a vlookup
F18: Pankaj Gupta:
You can't Pull a vlookup