Task - Combining or Joining Text.xlsx

	B	C	D	E	F	G	H	I	J	L	M	N	O	P	Q	S	T	U	V	W
1
2	Pan-India Vendor Payment & Mailing List
3
4	Vendor ID	First Name	Last Name	Street	City	State	PIN Code	Payment (₹)	Payment Date	Full Name (&)	Formal Name (CONCAT)	Mailing Address (CONCATENATE)	Payment Summary (TEXTJOIN)	Vendor Tag	List all vendors from Karnataka (TEXTJOIN)	Task No.+S3:W18	Task Description	Function to Use	Formula to Enter (for Column J2 onward)	Purpose & Output Example
5	V101	Rajesh	Kumar	45 Gandhi Nagar	Jaipur	Rajasthan	302001	15,250	1-Nov-23							1	Create a Full Name	Ampersand (&)	=[@[First Name]] & " " & [@[Last Name]]	Rajesh Kumar
6	V102	Priyanka	Singh	12/B Park Street	Kolkata	West Bengal	700016	8,900	2-Nov-23							1	Create a Full Name	Ampersand (&)	=[@[First Name]] & " " & [@[Last Name]]	Simple, most common method.
7	V103	Arjun	Reddy	Plot No. 78, Hi-Tech City	Hyderabad	Telangana	500081	120,000	1-Nov-23							2	Create a Formal Full Name (Mr./Ms.)	CONCAT	=CONCAT("Mr. ", [@[First Name]], " ", [@[Last Name]])	Mr. Rajesh Kumar
8	V104	Meera	Iyer	1st Cross, Malleshwaram	Bengaluru	Karnataka	560003	22,500	3-Nov-23							2	Create a Formal Full Name (Mr./Ms.)	CONCAT	=CONCAT("Mr. ", [@[First Name]], " ", [@[Last Name]])	CONCAT replaces &. Good for simple joins.
9	V105	Amit	Sharma	Shop No. 5, Sadar Bazaar	New Delhi	Delhi	110006	7,800	2-Nov-23							3	Generate a Mailing Address in a single cell	CONCATENATE	=CONCATENATE([@[First Name]], " ", [@[Last Name]], CHAR(10), [@Street], CHAR(10), [@City], " - ", [@[PIN Code]], CHAR(10), [@State])	Rajesh Kumar
10																				45 Gandhi Nagar
11																				Jaipur - 302001
12																				Rajasthan
13																				CHAR(10) adds a line break. CONCATENATE is old but works.
14																4	Create a Payment Summary with text	TEXTJOIN	=TEXTJOIN(" ", TRUE, "Pay", TEXT([@[Payment (₹)]], "₹#,##0"), "to", [@[First Name]], "on", TEXT([@[Payment Date]], "dd-mmm-yy"))	Pay ₹15,250 to Rajesh on 01-Nov-23
15																4	Create a Payment Summary with text	TEXTJOIN		Powerful: joins ranges, ignores blanks, uses a delimiter.
16																5	Create a Unique Vendor Tag	Ampersand (&) & TEXT	="IN-" & [@State] & "-" & TEXT([@[Payment Date]], "DDMM") & "-" & LEFT([@[Vendor ID]], 3)	IN-Rajasthan-0101-V101
17																5	Create a Unique Vendor Tag	Ampersand (&) & TEXT		Creates a unique ID for accounting records.
18																6	List all vendors from Karnataka	TEXTJOIN (Single Formula)	Meera Iyer	Meera Iyer
19																6	List all vendors from Karnataka	TEXTJOIN (Single Formula)	Meera Iyer	This is an array formula to list all names matching a criteria, separated by commas.
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100