TEXT

Hello Friends,

Today I will discuss about such a function which is not only very small but when implemented it can help you a lot.

The function is TEXT Function.

The Excel TEXT function returns a number in a specified number format, as text. It sounds very simple and plain sentence to us, let us learn how we can use TEXT function in our Excel formula.

This function can convert any number to string or text.

Syntax

The syntax for the TEXT function in Microsoft Excel is as under.

=TEXT(value, format)

Parameters or Arguments

value

The value to convert to text.

format

The format used to display the result.

Returns

The TEXT function returns a string/text value.

Example 1 :

How to use TEXT function?

The basic function of Text function is to convert any format data to text format. Below examples of TEXT function is on Date format.

Suppose you are given with few dates in a column and it is required to convert date format, say 23-04-18 to 23-Apr-18. Using TEXT function, you can do it in few seconds. Below picture will illustrate this feature better.

How to use TEXT function

Example 2 :

How to concatenate Short Date with Text?

We occasionally want to print the date automatically with a string, like Dashboard as on 23-Apr-18.

We can do it in two ways…

First : Placing date in one cell and the text in another cell then use CONCATENATE or CONCAT with TEXT function as shown in the below image. But it is also not so pleasing. We need some more automation. So, you can use the second method.

How to Using TEXT with CONCAT to display Dynamic Date with Texts or Strings

Second : More automation!!!

In the above example our excel formula is =CONCAT(A2," : ",TEXT(B2,"dd-mmm-yy")).

Now we will replace the cell reference ‘B2’ by another function TODAY() and we are done.  The final excel formula becomes =CONCAT(A2," : ",TEXT(TODAY(),"dd-mmm-yy")) as shown in the below picture.

How to Use TEXT function with CONCAT function to display Dynamic Date with Texts or Strings

Example 3 :

How to put zero before any integer number?

How to pad number with zero?

This was very useful when I was at telecom sector. We occasionally needed to put zeros before few numbers. Let us learn this.

Suppose we need to convert few numbers in 5 digits but the umber may be any length number. Say, we have numbers as 123,1234,12 and so on. If we want to put zeros before these numbers we must use TEXT function as show in the below image.

Note : You should use that number of ‘0’s which matches the length of the desired number.

Below image will describe it better.

Pad numbers with zeros using TEXT function

Hope this discussion or tutorial was helpful to you….

Thanks for reading…