Excel Text Function and How to Format Numbers and Dates

Excel contains a number functions which allow you to manipulate the data within your spreadsheet. One of the functions that allow for easy data manipulation is the text function.

The following tutorial will show you how to manipulate your data within excel using the text function. To follow the tutorial and apply the concepts taught in the tutorial, a sound basic understanding of Excel is required. Excel for beginners will teach you the basic elements of excel and will allow you to follow along with the examples in this tutorial.

How to Access the Excel Functions

Functions in excel allow you to manipulate the data within your spreadsheet in some way. Functions are accessed in various ways in excel.

Functions can be added directly to cells using the equal sign. Typing an equal sign in a cell brings up a list of functions that can be used in the spreadsheet. The functions appear as you begin to type. To access the text function for example, you can type =T in a cell and a list of functions starting with ‘T’ will appear. Take a look at the following screenshot:

You can see text is listed as one of the functions. If you access the function via the cell, excel provides syntax as a guide for you to follow to create the formula.

An easier way to access a function in excel is by using the Formulas tab on the menu in excel. Choose the formula tab and then choose the function you wish to apply. You can see the TEXT menu option under the Formulas tab contains a number of text functions. Take a look at the screenshot below:

When you access the function via the formula tab, Excel uses a formula wizard to help you to create the formula you need. If you are new to working with formulas then using the formula wizard can help you to quickly add formulas to your cells.

Let us use the formula wizard to convert the following dates into formatted text. The examples will be based on the spreadsheet below:

To convert the first date to a text field, click on cell B2. This is where we want our formatted text to display. Then open the text formula via the formula tab. Choose cell A2 in the value field to convert the first date and then type in “m” in the format text field to convert our date to a single digit number that represents the month.

Now let us add some more date formatting via the wizard. If we type in “mm” instead of “m” the text will appear as a double digit month:

If we type in “mmm”:

The text will appear as Aug:

The advantage to using the formula wizard is that it tells you what formula to use, which cell to use and also gives you a preview of what the formatted cell will look like at the bottom left hand corner of the wizard:

Below the preview of the formula is a link to the help function which gives you all the text formulas that can be used to format a date cell as  text within a cell.

Here are the various formulas that apply to dates:

Here is our date formatted using the various formats to change dates to text:

Formatting Numbers with the Excel Text Function

The text function also allows you to format numbers. Excel provides three characters to allow you to format numbers as text. They are the zero character, the hash character and the question mark. Each of the characters has a different effect on the numbers being formatted.

The zero character displays the number as is unless there are fewer digits than the format, in which case it places a zero within the text display of the number. Take a look at the example below. We are going to format 7.50 as a text value and then 7.5 as a text value using the 0.00 mask. You can see in the second example, excel adds a zero to the end of the number 7.5 because the number does not have sufficient digits.

The result of the above format on our two numbers is as follows:

Using the zero mask as a way to format number is a great way to standardize the way the numbers look in your worksheet.

The hash mask removes excess zeros if they appear in front of a number or after a decimal point at the end of the number. If you format the above numbers using the “##,##” mask then you would see the following result:

And finally the question mark mask aligns the numbers using the decimal point of each number. Using “??.??” To format the above numbers would result in the following:

For more tuition on how to use formulas within Excel, a course like mastering MS Excel will teach you how to use these powerful tools in a weekend.

The zero, hash and question mark may also be used in combination to format numbers as text in our spreadsheet. If we used the “$000,###” mask to format our numbers, then we would end up with the following result:

Using the format wizard is a great way to learn how to use the formulas within excel, but there are certain drawbacks to using the formula wizard. If you want to include other formulas within the cell then it is easier to type the formula within the cell itself. Suppose we want to format our number as text within another text statement. Take a look at the worksheet below:

Suppose we would like to construct a sentence that says Chris earned $5.25 in August this year. To format a sentence like that it is easier to type the formula for the sentence directly in the cell. Take a look at the formula below:

In the above formula we have used the text function to formulate the dollar amount as text using the “$##,00” mask and we used the “mmmm” mask to convert 15-Aug to the text “August”. To learn how to use formulas within formulas and more advanced functions, you can try an advanced excel course.

The result of the above formula in our worksheet is as follows:

As you can see from the examples given, the excel text function is a powerful way of converting numbers and dates to text. One thing to bear in mind though is once the date or number has been formatted as text, then it cannot be used for calculations like numbers can.

Formulas are what convert an ordinary spreadsheet in Excel to an extraordinary use of the application.To learn how to program advanced programming applications with Excel, you can enroll for an Excel formulas course.