All Text Formula in Excel

Text Formula

Days

=text(A2,”DDDD”)

Month

=text(A2,”MMMM”)

Year

=text(A2,”YYYY”)

 

	A	B	C	D
1	Date of Birth	Days	Months	Year
2	12/10/1995	Sunday	December	1995
3	10/10/1991
4	23/5/1995
5	3/6/1993
6	2/5/2001
7	8/9/2006

 

Concatenate Formula

=CONCATENATE(A2," ",B2," ",C2," ",D2)

	A	B	C	D	E
1	STATE	CITY	PINCODE	COUNTRY	COMPLETE ADDRESS
2	state 1	city 1	xyz	country 1	state 1 city 1 xyz country 1
3	state 2	city 2	xyz	country 2	state 2 city 2 xyz country 2
4	state 3	city 3	xyz	country 3	state 3 city 3 xyz country 3
5	state 4	city 4	xyz	country 4	state 4 city 4 xyz country 4

 

 

Averageif Formula

Average Qty.

=Averageif(B5:B14,A2,D5:D14)

 

Average Amount.

=Averageif(B5:B14,A2,F5:F14)

 

	A	B	C	D	E	F
1	Item Name			Av. Qty	Av. Amount
2	Mouse			18.25	3712.5
3
4	Item ID	Item	Brand	Qty	Price	Amount
5	100	Mouse	Abc	10	200	2000
6	101	Printer	Abc	20	1300	26000
7	102	Mouse	Xyz	25	210	5250
8	103	Monitor	Abc	11	2000	22000
9	104	Monitor	Xyz	13	2300	29900
10	105	Keyboard	Abc	29	310	8990
11	106	Keyboard	Xyz	30	270	8100
12	107	Mouse	Xyz	20	200	4000
13	108	Scanner	Abc	10	1700	17000
14	109	Scanner	Xyz	15	2150	32250
15	110	Mouse	Xyz	18	200	3600
16	111	Scanner	Xyz	19	1700	32300