0202 Text Function

Advanced Excel Dashboard Crash Course Section 2: 1st Dashboard using Pivot
6 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€92.78
List Price:  €129.90
You save:  €37.11
£79.40
List Price:  £111.16
You save:  £31.76
CA$136.11
List Price:  CA$190.56
You save:  CA$54.44
A$154.13
List Price:  A$215.78
You save:  A$61.65
S$135.08
List Price:  S$189.12
You save:  S$54.03
HK$782.28
List Price:  HK$1,095.23
You save:  HK$312.94
CHF 90.61
List Price:  CHF 126.85
You save:  CHF 36.24
NOK kr1,085.23
List Price:  NOK kr1,519.37
You save:  NOK kr434.13
DKK kr692.01
List Price:  DKK kr968.84
You save:  DKK kr276.83
NZ$167.80
List Price:  NZ$234.94
You save:  NZ$67.13
د.إ367.19
List Price:  د.إ514.08
You save:  د.إ146.89
৳10,976.08
List Price:  ৳15,366.96
You save:  ৳4,390.87
₹8,339.52
List Price:  ₹11,675.66
You save:  ₹3,336.14
RM473.25
List Price:  RM662.57
You save:  RM189.32
₦141,842.81
List Price:  ₦198,585.61
You save:  ₦56,742.80
₨27,810.04
List Price:  ₨38,935.18
You save:  ₨11,125.13
฿3,647.70
List Price:  ฿5,106.92
You save:  ฿1,459.22
₺3,232.12
List Price:  ₺4,525.11
You save:  ₺1,292.98
B$499.21
List Price:  B$698.91
You save:  B$199.70
R1,908.54
List Price:  R2,672.04
You save:  R763.49
Лв180.65
List Price:  Лв252.92
You save:  Лв72.26
₩135,197.71
List Price:  ₩189,282.20
You save:  ₩54,084.49
₪368.63
List Price:  ₪516.10
You save:  ₪147.47
₱5,633.91
List Price:  ₱7,887.71
You save:  ₱2,253.79
¥15,144.47
List Price:  ¥21,202.86
You save:  ¥6,058.39
MX$1,659.40
List Price:  MX$2,323.22
You save:  MX$663.82
QR364.31
List Price:  QR510.04
You save:  QR145.73
P1,370.91
List Price:  P1,919.33
You save:  P548.42
KSh13,148.68
List Price:  KSh18,408.68
You save:  KSh5,260
E£4,729.52
List Price:  E£6,621.52
You save:  E£1,892
ብር5,680.63
List Price:  ብር7,953.11
You save:  ብር2,272.48
Kz83,612.74
List Price:  Kz117,061.18
You save:  Kz33,448.44
CLP$97,978.20
List Price:  CLP$137,173.40
You save:  CLP$39,195.20
CN¥722.95
List Price:  CN¥1,012.16
You save:  CN¥289.21
RD$5,921.50
List Price:  RD$8,290.34
You save:  RD$2,368.83
DA13,490.83
List Price:  DA18,887.70
You save:  DA5,396.87
FJ$226.12
List Price:  FJ$316.58
You save:  FJ$90.46
Q779.86
List Price:  Q1,091.83
You save:  Q311.97
GY$20,923.51
List Price:  GY$29,293.76
You save:  GY$8,370.24
ISK kr13,946.60
List Price:  ISK kr19,525.80
You save:  ISK kr5,579.20
DH1,013.19
List Price:  DH1,418.51
You save:  DH405.32
L1,763.34
List Price:  L2,468.75
You save:  L705.40
ден5,702.11
List Price:  ден7,983.18
You save:  ден2,281.07
MOP$805.89
List Price:  MOP$1,128.28
You save:  MOP$322.39
N$1,893.44
List Price:  N$2,650.90
You save:  N$757.45
C$3,681.15
List Price:  C$5,153.75
You save:  C$1,472.60
रु13,335.63
List Price:  रु18,670.42
You save:  रु5,334.78
S/370.84
List Price:  S/519.19
You save:  S/148.35
K382.72
List Price:  K535.82
You save:  K153.10
SAR375
List Price:  SAR525.01
You save:  SAR150.01
ZK2,522.76
List Price:  ZK3,531.96
You save:  ZK1,009.20
L461.43
List Price:  L646.02
You save:  L184.59
Kč2,350.75
List Price:  Kč3,291.15
You save:  Kč940.39
Ft36,729.02
List Price:  Ft51,422.10
You save:  Ft14,693.08
SEK kr1,071.30
List Price:  SEK kr1,499.86
You save:  SEK kr428.56
ARS$85,766.82
List Price:  ARS$120,076.98
You save:  ARS$34,310.16
Bs691.04
List Price:  Bs967.48
You save:  Bs276.44
COP$387,583.68
List Price:  COP$542,632.66
You save:  COP$155,048.97
₡50,832.34
List Price:  ₡71,167.31
You save:  ₡20,334.97
L2,468.78
List Price:  L3,456.40
You save:  L987.61
₲737,805.73
List Price:  ₲1,032,957.54
You save:  ₲295,151.80
$U3,781.90
List Price:  $U5,294.82
You save:  $U1,512.91
zł400.73
List Price:  zł561.05
You save:  zł160.31
Already have an account? Log In

Transcript

Hello everybody. Welcome back to the dashboard course. In this video, we will learn a function that is used to make our life easy in dashboard for that matter anywhere in the corporate world. The function I'm talking about is text. A text is a formula that can extract any relevant data from a particular field, or value which are numbers or dates. Now let's see how we would use the text function in the current example.

We will be using the text function to extract the date from the given fields. Now in the example that I have here, we have dates from May 6 to 24 November 2015 to 2021. The first problem that we encounter is it may be difficult to understand this particular data or the date To view this date in an easier form, more understandable format, simply select the entire data by using Ctrl Space. Select the whole column, use the shortcut Ctrl Shift hash. That is just above number three on the first numerical line just below the function. So when you do that, automatically, it converts the entire data column into a format that is easy to decipher.

Like now, it's simple enough to understand that it is the sixth of may 15, fifth of April 17 to 24th of November 21. Now, that's just the conversion part. But in a pivot table, when we do any kind of pivot table or reporting, pivot requires the data separately so they can be show on the pivot or the framework. To do that we require variables differently. Like if I wish to see the number six, that is the day separately, I may have to use a function. And if I wish to see which week day it is, say Sunday or Monday, I may have to use another formula.

So let's see both of them. To extract the day we can use the formula equals day, bracket open, it requires a serial number. Serial Number is the day. That's to the left, select bracket close, enter, extracted the day from the given date. So it's the sixth. But what if I wanted an actual day like Sunday or Monday, Tuesday, so on and so forth, but I have to use the function text.

Now how does that work? It equals text, open the bracket. First I have to select the date that the value comma, later, we have to enter the format. Now make Excel understand the file format as we do understand. So formatting, customized. We specify the format, dd mm yy.

It requires that format as well. So under double quotation do remember, it has to be under double quotations. Put in the format like you can wish to have date, just put in three DS for a shorter version, something like this. Just close the quotation, bracket closed, enter, so you get the relevant answer. If we wish to see the full format, that is Wednesday, simply press f2 to go into the cell and just put in the D four times to the frequency of the D that we can use a single, double, triple or four times where it provides the relevant answer. Now if single D gives me one digit numbers, Double D gives a formatted number one to 209 If I go for the third D, it gives me the shorter version of the day.

And last but not least is the fourth D, that gives us the full form. That is Wednesday. Now, the same is applicable for the month, and the same goes for the year as well. Let's try it for the month. It's simple equals two. Now if I want the answer in a number, I can go with a month formula.

Select the serial number that is a date, bracket closed, enter, and that gives me the month numbers. But do remember if you're using the text formula, the answers are not a number, but rather in text. Now as you can see the alignment is to the left. But the day is to the right because that by default in Excel, the numbers are on the right, and text is aligned to the left. So if you are using the text function, the answer that it provides his text and not a number. Now keep that in mind.

Now let's Come down to the month. So equals to once again text I select the date, comma, and double quotations this time I wish to have the month so I use triple M is not case sensitive so it does not matter whether the M is capital or small, close the bracket and enter. So I'll just change this over here and convert this to 3ds. Now, that should do it. Last but not least is here. Now here either we can go with text selecting the value comma and double quotation we can go with two y's and that will give me the last two digits that is 15 1718.

Or I can go with four wise that will give me the entire year. That is 2015 2014 2017, so on and so forth. But as we can see the answer is in text. To have the answer in number it's a very simple formula equals two year select the date bracket close, enter. As simple as that, and once done, simply select the whole range which has a formula using the fill handle. And just double click and your formula is applied for all the data.

So I'll just align it to the center just to make the data more presentable. In the next video, we will see how to start with a pivot that is dynamic and understand the expansion of the data itself. Till then keep practicing. I'll see you in the next video. Thank you

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.