Excel Formulas and Data Analysis Course: Text and date functions

1. Pergunta 1 de 17

   In the staff list below, what does =LEFT(B2,3) return?

   	A	B	C	D
   1	Name	ID	Start date	Email
   2	Priya Nair	CGY-0412	2019-06-15	priya.nair@example.com
   3	Jo Tran	EDM-0087	2023-11-06	jo.tran@example.com
   4	Leo Martin	RED-1150	2025-02-03	leo.martin@example.com

   • ACGY-
   • BCGY
   • CC
   • D412
   Mostrar resposta

   Resposta: B. CGY

   LEFT returns characters from the start of the text. The first 3 characters of CGY-0412 are CGY.

2. Pergunta 2 de 17

   In the staff list below, what does =RIGHT(B3,4) return?

   	A	B	C	D
   1	Name	ID	Start date	Email
   2	Priya Nair	CGY-0412	2019-06-15	priya.nair@example.com
   3	Jo Tran	EDM-0087	2023-11-06	jo.tran@example.com
   4	Leo Martin	RED-1150	2025-02-03	leo.martin@example.com

   • AEDM-
   • B0087
   • C-0087
   • D87
   Mostrar resposta

   Resposta: B. 0087

   RIGHT returns the last 4 characters as text, so the leading zeros stay: 0087.

3. Pergunta 3 de 17

   In the staff list below, what does =MID(B4,5,2) return?

   	A	B	C	D
   1	Name	ID	Start date	Email
   2	Priya Nair	CGY-0412	2019-06-15	priya.nair@example.com
   3	Jo Tran	EDM-0087	2023-11-06	jo.tran@example.com
   4	Leo Martin	RED-1150	2025-02-03	leo.martin@example.com

   • A11
   • B-1
   • CRE
   • D15
   Mostrar resposta

   Resposta: A. 11

   MID starts at character 5 and takes 2 characters. In RED-1150, characters 5 and 6 are 1 and 1.

4. Pergunta 4 de 17

   In the staff list below, what does =LEN(A3) return?

   	A	B	C	D
   1	Name	ID	Start date	Email
   2	Priya Nair	CGY-0412	2019-06-15	priya.nair@example.com
   3	Jo Tran	EDM-0087	2023-11-06	jo.tran@example.com
   4	Leo Martin	RED-1150	2025-02-03	leo.martin@example.com

   • A5
   • B6
   • C7
   • D2
   Mostrar resposta

   Resposta: C. 7

   LEN counts every character, and spaces count. "Jo Tran" has 6 letters and 1 space.

5. Pergunta 5 de 17

   Cell A4 in the staff list below has extra spaces. What does =TRIM(A4) return?

   	A	B	C	D
   1	Name	ID	Start date	Email
   2	Priya Nair	CGY-0412	2019-06-15	priya.nair@example.com
   3	Jo Tran	EDM-0087	2023-11-06	jo.tran@example.com
   4	Leo Martin	RED-1150	2025-02-03	leo.martin@example.com

   • ALeo Martin
   • BLeoMartin
   • CLeo Martin
   • DLeo
   Mostrar resposta

   Resposta: A. Leo Martin

   TRIM removes spaces at the start and end and leaves single spaces between words.

6. Pergunta 6 de 17

   Which letters are capitals in the result of =PROPER("jean-luc ROY")?

   • AJ and R only
   • BJ, R, O and Y
   • CJ, L and R
   • DNone; PROPER only removes spaces
   Mostrar resposta

   Resposta: C. J, L and R

   PROPER capitalizes the first letter and any letter after a non-letter (such as a hyphen or space), and makes all other letters lowercase, so the result is Jean-Luc Roy.

7. Pergunta 7 de 17

   What does =CONCAT("Priya","Nair") return?

   • APriya&Nair
   • BPriya Nair
   • CPriyaNair
   • DPriya,Nair
   Mostrar resposta

   Resposta: C. PriyaNair

   CONCAT joins the text exactly as given and does not add a delimiter. Add " " between the parts, or use TEXTJOIN, to get a space.

8. Pergunta 8 de 17

   In the sheet below, A3 is empty. What does =TEXTJOIN(", ",TRUE,A2:A5) return?

   	A
   1	Site
   2	Calgary
   3
   4	Red Deer
   5	Lethbridge

   • ACalgary, , Red Deer, Lethbridge
   • BCalgary, Red Deer, Lethbridge
   • CCalgaryRed DeerLethbridge
   • DCalgary, Red Deer
   Mostrar resposta

   Resposta: B. Calgary, Red Deer, Lethbridge

   TEXTJOIN puts the delimiter ", " between items. With ignore_empty set to TRUE, the empty cell is skipped.

9. Pergunta 9 de 17

   What does =SEARCH("lock","Smart Lock") return?

   • A7
   • B6
   • C#VALUE!
   • D1
   Mostrar resposta

   Resposta: A. 7

   SEARCH is not case sensitive, so "lock" matches "Lock", which starts at the 7th character (the space is character 6).

10. Pergunta 10 de 17

    You need to find text in a cell, and uppercase and lowercase must match exactly. Which function should you use?

    • ATRIM
    • BPROPER
    • CSEARCH
    • DFIND
    Mostrar resposta

    Resposta: D. FIND

    SEARCH ignores case. Microsoft says to use FIND when you need a case-sensitive search.

11. Pergunta 11 de 17

    You join a label and a date with =A2&" "&B2, and the date shows as a number such as 46083. What fixes this?

    • AFormat the result cell as Short Date
    • BUse SUM(A2,B2) instead of the & operator
    • CUse TEXT(B2,"yyyy-mm-dd") in the formula
    • DTurn on Wrap Text for the result cell
    Mostrar resposta

    Resposta: C. Use TEXT(B2,"yyyy-mm-dd") in the formula

    When numbers are joined to text, Excel drops their number format. TEXT turns the date into text in the format you choose.

12. Pergunta 12 de 17

    How does Excel store a date such as January 1, 1900?

    • AAs the number of seconds since 1970
    • BAs plain text, like a label
    • CAs a serial number
    • DAs three numbers: year, month and day
    Mostrar resposta

    Resposta: C. As a serial number

    Excel stores dates as serial numbers so it can calculate with them. January 1, 1900 is serial number 1.

13. Pergunta 13 de 17

    What date does =DATE(2026,14,1) return?

    • A2027-02-01
    • B2026-12-01
    • C2026-02-01
    • D#NUM!
    Mostrar resposta

    Resposta: A. 2027-02-01

    A month over 12 rolls into the next year. Month 14 of 2026 is February 2027.

14. Pergunta 14 de 17

    March 2, 2026 is a Monday. What does =WEEKDAY(DATE(2026,3,2)) return?

    • A7
    • B2
    • C0
    • D1
    Mostrar resposta

    Resposta: B. 2

    With no return_type, WEEKDAY numbers the days 1 (Sunday) to 7 (Saturday), so Monday is 2. Use return_type 2 to make Monday 1.

15. Pergunta 15 de 17

    A2 holds 2026-03-02, B2 holds 2026-03-13 and C2 holds a holiday, 2026-03-09. What does =NETWORKDAYS(A2,B2,C2) return?

    • A10
    • B9
    • C8
    • D11
    Mostrar resposta

    Resposta: B. 9

    March 2 to 13 has 10 weekdays, counting both the start and end dates. The holiday on Monday, March 9 is removed, leaving 9.

16. Pergunta 16 de 17

    What does =EOMONTH(DATE(2026,2,10),0) return?

    • A2026-02-10
    • B2026-02-28
    • C2026-03-31
    • D2026-02-29
    Mostrar resposta

    Resposta: B. 2026-02-28

    EOMONTH returns the last day of the month, 0 months away. 2026 is not a leap year, so February ends on the 28th.

17. Pergunta 17 de 17

    In the staff list below, what does =DATEDIF(C2,DATE(2026,3,2),"Y") return?

    	A	B	C	D
    1	Name	ID	Start date	Email
    2	Priya Nair	CGY-0412	2019-06-15	priya.nair@example.com
    3	Jo Tran	EDM-0087	2023-11-06	jo.tran@example.com
    4	Leo Martin	RED-1150	2025-02-03	leo.martin@example.com

    • A2452
    • B7
    • C80
    • D6
    Mostrar resposta

    Resposta: D. 6

    The "Y" unit counts complete years. From June 15, 2019 to March 2, 2026 is 6 full years; the 7th year is not complete until June 15, 2026.

Teclado: 1–4 para responder, Enter para a pergunta seguinte.