INTRODUCTION
The TEXT function in Excel is a powerful and versatile feature that allows you to format and display cell contents as text in a specific way. This function is particularly useful when you want to present data in a more human-readable format, such as dates, numbers, or custom text strings. By using the TEXT function, you can convert numeric values or dates into various formats, making your Excel spreadsheets more user-friendly and visually appealing.
1. Basic Date Formatting:
- Formula: =TEXT(A1, “MM/DD/YYYY”)
- Example: Cell A1 contains 10/28/2023. The formula will display 10/28/2023.
2. Custom Date Formatting:
- Formula: =TEXT(A1, “DD MMM, YYYY”)
- Example: Cell A1 contains 10/28/2023. The formula will display 28 Oct, 2023.
3. Time Formatting:
- Formula: =TEXT(A1, “hh:mm AM/PM”)
- Example: Cell A1 contains 18:30. The formula will display 06:30 PM.
4. Number Formatting:
- Formula: =TEXT(A1, “$#,##0.00”)
- Example: Cell A1 contains 1234.5678. The formula will display $1,234.57.
5. Percentage Formatting:
- Formula: =TEXT(A1, “0.00%”)
- Example: Cell A1 contains 0.4567. The formula will display 45.67%.
6. Scientific Notation Formatting:
- Formula: =TEXT(A1, “0.00E+00”)
- Example: Cell A1 contains 12345. The formula will display 1.23E+04.
7. Custom Text Concatenation:
- Formula: =TEXT(A1, “MMMM DD, YYYY”) & ” – ” & TEXT(B1, “0.00”)
- Example: Cell A1 contains 10/28/2023 and B1 contains 123.45. The formula will display October 28, 2023 – 123.45.
8. Conditional Text Formatting:
- Formula: =IF(A1<>””, “Date Entered: ” & TEXT(A1, “MM/DD/YYYY”), “”)
- Example: Cell A1 contains 10/28/2023. The formula will display Date Entered: 10/28/2023.
9. Extracting Month and Year:
- Formula: =TEXT(A1, “MMMM YYYY”)
- Example: Cell A1 contains 10/28/2023. The formula will display October 2023.
10. Adding Leading Zeros:
- Formula: =TEXT(A1, “0000”)
- Example: Cell A1 contains 42. The formula will display 0042.
11. Displaying Day of the Week:
- Formula: =TEXT(A1, “dddd”)
- Example: Cell A1 contains 10/28/2023. The formula will display Saturday.
12. Extracting First Letter:
- Formula: =LEFT(TEXT(A1, “MMMM”), 1)
- Example: Cell A1 contains 10/28/2023. The formula will display O.
13. Extracting Last Three Characters:
- Formula: =RIGHT(TEXT(A1, “MMMM”), 3)
- Example: Cell A1 contains 10/28/2023. The formula will display ber.
14. Converting Text to Uppercase:
- Formula: =UPPER(TEXT(A1, “mmmm”))
- Example: Cell A1 contains October. The formula will display OCTOBER.
15. Converting Text to Lowercase:
- Formula: =LOWER(TEXT(A1, “MMMM”))
- Example: Cell A1 contains OCTOBER. The formula will display october.
16. Removing Spaces:
- Formula: =SUBSTITUTE(TEXT(A1, “MM/DD/YYYY”), ” “, “”)
- Example: Cell A1 contains 10/28/2023. The formula will display 10/28/2023.
17. Extracting Specific Characters:
- Formula: =MID(TEXT(A1, “MM/DD/YYYY”), 4, 2)
- Example: Cell A1 contains 10/28/2023. The formula will display 28.
18. Displaying File Extensions:
- Formula: =RIGHT(A1, LEN(A1)-FIND(“.”, A1))
- Example: Cell A1 contains document.txt. The formula will display txt.
19. Formatting as Postal Code:
- Formula: =TEXT(A1, “00000”)
- Example: Cell A1 contains 123. The formula will display 00123.
20. Extracting First Word:
- Formula: =LEFT(TEXT(A1, “@”), FIND(” “, TEXT(A1, “@”))-1)
- Example: Cell A1 contains Hello World. The formula will display Hello.
21. Extracting Last Word:
- Formula: =RIGHT(TEXT(A1, “@”), LEN(TEXT(A1, “@”))-FIND(“@”, SUBSTITUTE(TEXT(A1, “@”), ” “, “@”)))
- Example: Cell A1 contains Hello World Excel. The formula will display Excel.
22. Displaying Ordinal Numbers:
- Formula: =TEXT(A1, “0”) & IF(OR(TEXT(A1, “dd”)=11, TEXT(A1, “dd”)=12, TEXT(A1, “dd”)=13), “th”, CHOOSE(MATCH(TEXT(A1, “dd”), {“1″,”2″,”3”}, 0), “st”, “nd”, “rd”) & “th”) & TEXT(A1, ” mmmm, YYYY”)
- Example: Cell A1 contains 10/28/2023. The formula will display 28th October, 2023.
23. Replacing Specific Text:
- Formula: =SUBSTITUTE(TEXT(A1, “@”), “old”, “new”)
- Example: Cell A1 contains The old car. The formula will display The new car.
24. Displaying Full Address:
- Formula: =TEXT(A1, “@, “) & TEXT(B1, “@, “) & TEXT(C1, “@”) & TEXT(D1, “, @ @”)
- Example: Cells A1 to D1 contain 123 Main St, Apt 4B, Springfield, and 12345 respectively. The formula will display 123 Main St, Apt 4B, Springfield, 12345.
25. Formatting Phone Numbers:
- Formula: =TEXT(A1, “(000) 000-0000”)
- Example: Cell A1 contains 1234567890. The formula will display (123) 456-7890.
26. Displaying Age from Birthdate:
- Formula: =INT((TODAY() – A1) / 365.25)
- Example: Cell A1 contains 10/28/1990. The formula will calculate the age based on the current date.
27. Displaying Days Left to a Deadline:
- Formula: =A1 – TODAY()
- Example: Cell A1 contains 12/31/2023. The formula will calculate the number of days left until December 31, 2023.
28. Custom Date Format with Text:
- Formula: =TEXT(A1, “mmmm d, yyyy”) & ” is a ” & TEXT(A1, “dddd”)
- Example: Cell A1 contains 10/28/2023. The formula will display October 28, 2023 is a Saturday.
29. Displaying Quarter of the Year:
- Formula: =ROUNDUP(MONTH(A1)/3, 0)
- Example: Cell A1 contains 10/28/2023. The formula will display 4 for the fourth quarter.
30. Displaying Date as Day of the Year:
- Formula: =TEXT(A1 – DATE(YEAR(A1), 1, 1) + 1, “0”)
- Example: Cell A1 contains 10/28/2023. The formula will display 301 for the 301st day of the year.
31. Displaying Time in 12-hour Format:
- Formula: =TEXT(A1, “h:mm AM/PM”)
- Example: Cell A1 contains 18:30. The formula will display 6:30 PM.
32. Displaying Time in Minutes:
- Formula: =TEXT(A1, “[mm] minutes”)
- Example: Cell A1 contains 75. The formula will display 75 minutes.
33. Displaying Time Difference in Hours:
- Formula: =TEXT(A2-A1, “h”) & ” hours and ” & TEXT(MINUTE(A2-A1), “m”) & ” minutes”
- Example: Cell A1 contains 18:30 and A2 contains 22:15. The formula will display 3 hours and 45 minutes.
34. Displaying Decimal as Fraction:
- Formula: =TEXT(A1, “# ?/?”)
- Example: Cell A1 contains 1.75. The formula will display 1 3/4.
35. Displaying Date and Time:
- Formula: =TEXT(A1, “mm/dd/yyyy hh:mm AM/PM”)
- Example: Cell A1 contains 10/28/2023 18:30. The formula will display 10/28/2023 06:30 PM.
36. Displaying Yes/No Based on a Condition:
- Formula: =IF(A1>100, “Yes”, “No”)
- Example: Cell A1 contains 120. The formula will display Yes.
37. Displaying True/False Based on a Condition:
- Formula: =A1>B1
- Example: Cell A1 contains 5 and B1 contains 3. The formula will display TRUE.
38. Custom Date Formatting with Day Suffix:
- Formula: =DAY(A1) & IF(OR(DAY(A1)=11, DAY(A1)=12, DAY(A1)=13), “th”, CHOOSE(MATCH(DAY(A1), {1,2,3}, 0), “st”, “nd”, “rd”) & “th”) & ” ” & TEXT(A1, “mmmm, yyyy”)
- Example: Cell A1 contains 10/28/2023. The formula will display 28th October, 2023.
39. Displaying Age Group Based on Birthdate:
- Formula: =IF(A1<=DATE(YEAR(TODAY())-18,MONTH(TODAY()),DAY(TODAY())), “Adult”, “Minor”)
- Example: Cell A1 contains 10/28/2005. The formula will display Minor.
40. Extracting Month Name:
- Formula: =TEXT(A1, “mmmm”)
- Example: Cell A1 contains 10/28/2023. The formula will display October.
41. Displaying Decimal as Time:
- Formula: =TEXT(A1, “[h]:mm:ss”)
- Example: Cell A1 contains 25.5 (representing 25 hours and 30 minutes). The formula will display 25:30:00.
42. Displaying Month Abbreviation:
- Formula: =TEXT(A1, “mmm”)
- Example: Cell A1 contains 10/28/2023. The formula will display Oct.
43. Displaying Julian Date:
- Formula: =TEXT(A1, “yyyy”) & TEXT(A1-DATE(YEAR(A1),1,1)+1, “000”)
- Example: Cell A1 contains 10/28/2023. The formula will display 2023301.
44. Displaying Day Name as Three Letters:
- Formula: =TEXT(A1, “ddd”)
- Example: Cell A1 contains 10/28/2023. The formula will display Sat.
its Helpful for me