© 2024 - Image by Krys MurphyDo you need to have text rather than a number as a result of a calculation? Well the [TEXT] function is what you might use. In case you want to get a month name rather than a number, and normal formatting just doesn’t work for you, use the [TEXT] function again, but with a different date code:

If you need more explanation for the result shown in the cell, [TEXT] function is for you.

If you need to show which day of the week and the date then use this function.

Simplest use

You already have a calculation result in a cell i.e. B6, an example of a sheet below. simple calculation sheet

With the formulas showing the sheet looks like this. Formulas showing

We would like the words – The total for north region is – followed by the total number as shown in the cell E2 but with a £ sign in front. Text with result

The formula to use is this:

=”The total for “&A2&” region is £”&E2 Note

Note that the “&” is an abbreviation of the Concatenate function.

Filling in the formula down column F will result in this. Filled results

Using dates

You have a date in a cell but you need to show which day this was; here is the formula for that.

A7 is the cell with the date.

Your formula is in the cell of your choice.

=”This day was:”& TEXT(A7,” dddd, mmmm dd yyyy”)

Here is the formulas showing. Example 2

Here is the result without formulas showing. result 2

Change the order of the date code and you can get this result.

Formula.  Example 3

Result. Result 3

Using two cells of reference

To use two cells as items to calculate the display in one cell you could use the formula like this.

Text function 2 reference cells

The result will be.

Text function - Result 4

N.B.! The TEXT function converts a numeric value to a text string. Therefore, you won’t be able to use the result of your Text formula in another calculation.

More for your perusal:

Finding the cell that contains just a space in Excel

Tips – Validation lists in Excel

Have you lost your attachments in Outlook?

 

LEAVE A REPLY

Please enter your comment!
Please enter your name here