PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

CONTENTS

EXCEL FUNCTIONS-TEXT

INTRODUCTION

TEXT FUNCTION is found under the TEXT FUNCTION CATEGORY of Excel.

TEXT FUNCTION is specially created to format the values and make them more readable as per our requirement.

 

TEXT FUNCTION changes the format of the number and gives it custom format as per the code passed to the function.This function is useful at many places such as handling date and time. Removing time from the date or removing date from the long date etc.
Text function can also be used to combine text and number with any symbols etc.
 

 


PURPOSE OF TEXT FUNCTION IN EXCEL

TEXT FUNCTION allows us to format our number into more readable format. There are several standard codes by the use of which we can customize the format for better readability.

Simply saying it is just the way to change the format of the cell just like we change the format using the CHANGE FORMAT DIALOG BOX.

PREREQUISITES TO LEARN TEXT FUNCTION

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

 

  •  Basic understanding of how to use a formula or function.
  •  Basic understanding of rows and columns in Excel.
  • If we know the way Excel handles the DATES, the function becomes a bit easier to learn.
  •  Of course, Excel software.
Helpful links for the prerequisites mentioned above

SYNTAX: TEXT FUNCTION IN EXCEL

The Syntax for the TEXT function in Excel is

=TEXT ( VALUE TO BE FORMATTED , FORMAT CODE WHICH IS TO BE APPLIED )

VALUE TO BE FORMATTED This is the value on which we want to operate and change the format.

FORMAT CODE  is the code of the format which is to be applied on the given value.

DIFFERENT FORMATS IN TEXT FUNCTION

In simple words, TEXT FUNCTION changes the format of the cells. We can use any format using the Text function and perform various functions such as

  • applying leading zeros,
  • putting the comma after a fixed number of digits for currency
  • extracting date and time from a long date.
  • converting the format of date or time
  • creating any special phone numbers  and many more.
We’ll try them in the example discussed below.

CONCEPT OF TEXT FUNCTION IN EXCEL

The concept of text function is simple.

As the first argument, we have the value on which we want to operate. As the second argument , we tell the TEXT FUNCTION, in which format we want to see the value.

Read the following statement twice.

1. THE NUMBERS CAN BE DENOTED BY A 0 , # OR ? FOR TELLING THE TEXT FUNCTION  ABOUT THE FORMAT WE WANT.

THE TIME AND DATE RECOGNIZES HH FOR HOURS, MM FOR MINUTES, SS FOR SECONDS, MM FOR MONTHS DD FOR DATE AND YY OR YYYY FOR YEARS.

Keeping these points in mind let us try to put this theory into action.

Suppose we have a 9 (Nine) number digit say 1234546789 and we want to put a hyphen (-) at position after 2 digits and  6 digits from the right. Let us create format for this.

We’ll use the formula

=TEXT(123456789, “###-####-##”) or

=TEXT(123456789, “000-0000-00”) or

=TEXT(123456789, “???-????-??”)

All of them would give the result as 123-4567-89

Similarly , we can try any format. For the help we can use the FORMAT DIALOG BOX TOO. which can be opened easily by CTRL+1 or RIGHT CLICK>FORMAT CELLS.

Setting the custom format
EXCEL:CUSTOM FORMAT EXAMPLE

EXAMPLES :TEXT FUNCTION IN EXCEL

DATA SAMPLE

Let us try to convert various formats using the TEXT FUNCTION.

VALUEPLANNEDCONVERSIONFORMULA USED
5463CURRENCY WITH $ AND SEPARATOR$5,463=TEXT(F6,”$#,#00″)
3216546123() IN THE FIRST THREE DIGITS(321)-6546123=TEXT(F7,”(###)-######0″)
30-06-2020EXTRACT DATE AND MONTH30/06=TEXT(F8,”DD/MM”)
18:30:54EXTRACT HRS AND MINUTES$01=TEXT(F9,”$#,#00″)
    
30-06-2020PUT DATE IN YY/DD/MM FORMAT20/30/06=TEXT(F11,”YY/DD/MM”)
30-06-2020DAYTuesday=TEXT(F12,”DDDD”)
30-06-2020MONTHJune=TEXT(F13,”MMMM”)
0.321PERCENTAGE UPTO 1 DECIMAL32.1%=TEXT(F14,”#.0%”)
0.321PERCENTAGE UPTO 2 DECIMAL32.10%=TEXT(F15,”#.00%”)
4.25FRACTION4 1/4=TEXT(F16,”# ?/?”)
17.33FRACTION52/3=TEXT(F17,”?/?”)
18:53TIME WITH AM/PM6:53 PM=TEXT(F18,”H:MM AM/PM”)
    
TEXT FUNCTION TO CHANGE THE FORMAT
EXCEL:DIFFERENT EXAMPLES OF USING TEXT FUNCTIONS

STEPS TO USE TEXT FUNCTION

Many examples has been shown above to use the TEXT FUNCTION.

The generalized steps are following.

  •  Place the cursor where you want to get the output.
  • Put the formula =Text(Value, “Format in the way you want the output”).
  • The answer would appear.
For the reference , the above examples can be taken for help. The list is not complete but just to give an idea so that you can try them yourself.

GETTING HELP FOR FORMATS TO BE USED IN TEXT FUNCTION

If you are confused about any type of format, you can always get help from Excel itself.

  • Go to the cell.
  • Right Click >Format Cells
  • Go to Custom
  • An exhaustive list is already there which can be used too.
  • Just right click the format and paste in your function.
FIND THE CODE FOR TEXT FUNCTON FROM FORMAT CELLS DIALOG BOX

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

gyankosh060309@gmail.com

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

%d bloggers like this: