Excel’s TEXT Functions with Examples
Excel’s TEXT functions are some of the most powerful and useful tools available in Excel. They enable users to manipulate text strings, perform calculations on characters, and format dates, times, and numbers in various ways. This article will provide an overview of Excel’s TEXT functions, with examples to demonstrate their use. While reading about text functions remember to keep your sheets tidy and nice looking, read some tips in our article about Excel data cleaning tools.
Excel’s TEXT functions fall into two categories: String Manipulation and Date/Time Manipulation. String Manipulation functions allow users to manipulate text strings in various ways, such as changing case, extracting text from a string, replacing characters, and more. Date/Time Manipulation functions allow users to convert dates and times into various formats, such as words, numbers, and more.
String Manipulation Functions
Excel’s string manipulation functions enable users to manipulate text strings in various ways.
The LEN function returns the length of a text string. For example, the formula =LEN(“Hello World”) returns 11, the length of the string “Hello World”.
The LEFT and RIGHT functions extract a specified number of characters from the left or right side of a text string. For example, the formula =LEFT(“Hello World”, 5) returns “Hello”, while the formula =RIGHT(“Hello World”, 5) returns “World”.
The MID function extracts a specified number of characters from any position within a text string. For example, the formula =MID(“Hello World”, 6, 5) returns “World”.
The FIND and SEARCH functions locate a specified character within a text string. For example, the formula =FIND(“o”, “Hello World”) returns 4, the position of the first “o” in the string “Hello World”. If you’re looking for ways to search for data in your spreadsheet you might find our article about the VLOOKUP function valuable.
The TRIM function is to remove excess spaces from a text string. For example, the formula =TRIM(“Hello World ”) returns “Hello World”, without the extra spaces.
The UPPER, LOWER, and PROPER functions converts text strings to uppercase, lowercase, or proper case. For example, the formula =UPPER(“Hello World”) returns “HELLO WORLD”, while the formula =LOWER(“Hello World”) returns “hello world”, and the formula =PROPER(“hElLo wOrLd”) returns “Hello World”.
You can use the REPLACE and SUBSTITUTE functions to replace one character with another in a text string.
=REPLACE(“Hello World”, 5, 3, “Goodbye”) returns “Hello Goodbye”
=SUBSTITUTE(“Hello World”, “World”, “Universe”) returns “Hello Universe”.
Use the CONCATENATE function to join two or more text strings together see more examples in our learning section.
=CONCATENATE(“Hello”, “ ”, “World”) returns “Hello World”.
Take a look at our blog article about text functions that can help you extract data from text strings in Excel.
Date/Time Manipulation Functions
Excel’s date/time manipulation functions enable users to convert dates and times into various formats.
The TODAY function returns the current date.
=TODAY() returns the current date in the format “mm/dd/yyyy”.
The NOW function returns the current date and time.
=NOW() returns the current date and time in the format “mm/dd/yyyy hh:mm:ss”.
The DAY, MONTH, and YEAR functions can extract the day, month, and year from a date. For example, the formula =DAY(“01/01/2020”) returns 1, the formula =MONTH(“01/01/2020”) returns 1, and the formula =YEAR(“01/01/2020”) returns 2020. Using these formulas can help you view your data in a more organized way, here are some tips on how to use conditional formatting in your Excel spreadsheet.
The DATEVALUE function converts a text string into a date. For example, the formula =DATEVALUE(“01/01/2020”) returns the date “01/01/2020” in the format “mm/dd/yyyy”.
Use the TIMEVALUE function to convert a text string into a time. For example, the formula =TIMEVALUE(“12:00 PM”) returns the time “12:00 PM” in the format “hh:mm:ss”.
The TEXT function can convert a date or time into a text string. For example, the formula =TEXT(“01/01/2020”,”mm/dd/yyyy”) returns the text string “01/01/2020”, while the formula =TEXT(“12:00 PM”,”hh:mm:ss”) returns the text string “12:00 PM”.
Conclusion
Excel’s TEXT functions are some of the most powerful and useful tools available in Excel. They enable users to manipulate text strings, perform calculations on characters, and format dates, times, and numbers in various ways. This article has provided an overview of Excel’s TEXT functions, with examples to demonstrate their use. With these functions, users can easily manipulate text strings and dates/times in Excel. If you’re looking for more Excel functions make sure to visit our formula library here.
Learn more about Excel and Google Sheets hacks in other articles. Stay connected with us on social media and receive more daily tips and updates.
