IToverview.com - Easy To Learn Tutorials!

IToverview.com

Tutorials


Commonly used TEXT functions

The TEXT functions are listed under FORMULASArrowTEXT.

We have explained about different TEXT functions like upper, lower, proper, trim, len, left, right, exact, clean, fixed, find, search, concatenate etc.

Note: in our explanation, we have mentioned first argument, second argument, third argument, fourth argument etc. First argument always starts from the left.

UPPER:

If you need to change the typed character to upper case, you can use UPPER function. If you want to change John to JOHN. In any cell, type =; select upper from text functions; in the new screen, click text box; then click b5 cell, and click okay. You can see JOHN now

Example: =UPPER(John) would return JOHN

LOWER:

Similarly if you need to change the typed character to lower case, you can LOWER function.

Example: =LOWER (John) would return john



PROPER:

Similarly if you want to change the typed character to PROPER (first character upper and the other characters lower), you can PROPER function.

Example: =PROPER (john smith) would return John Smith

TRIM:

Working in real time, you get data with spaces. Example ( Vi vi an Rich ar ds; Actual name is Vivian Richards) You can use TRIM function to remove spaces in the words Vivian and Richards. It will not separate the spaces between words.

Example: =TRIM (Vi vi an Rich ar ds) would return Vivian Richards

LEN:

Counts the number of strings in that cell.

Example: =LEN (JOHN) would return 4

LEFT:

Left function has two arguments (Text, Num_Chars).

Text (First argument): You have to mention the text on which you are searching.

Num_Chars (Second Argument): You have to mention a numeric value, which will extract characters from the text mentioned in the first argument and display those characters. The number of extracted characters will be from the left and equal to that numeric value typed in the second argument.

Example:
=LEFT ("Information Technology", 5) would return Infor
=LEFT ("Information Technology", 14) would return Information Te

RIGHT:

Right function has two arguments (Text, Num_Chars).

Text (First argument): You have to mention the text on which you are searching.

Num_Chars (Second Argument): You have to mention a numeric value, which will extract characters from the text mentioned in the first argument and display those characters. The number of extracted characters will be from the right and equal to that numeric value typed in the second argument.

Example:
=RIGHT ("Information Technology", 5) would return ology
=RIGHT ("Information Technology", 14) would return ion Technology

EXACT:

It is a case sensitive function and compares whether two arguments are same.

Text1 (First Argument): You have to mention the text

Text2 (Second Argument) you have to mention the text

Example:
=EXACT("one","one") would return TRUE
=EXACT("One","one") would return FALSE



CLEAN:

When you import data into excel, you may get unwanted characters, which may not be seen by your eyes. To remove those unprintable characters, you use Clean function.

FIXED:

Fixed function has 3 arguments (Number, Decimals, and No_Commas)

Number (First Argument): A numerical value that has to be rounded.

Decimals (Second Argument): Number of digits to the right of the decimal point.

No_Commas (Third Argument): Whether you need COMMAS or not. If you specify TRUE, commas will not be there. If you specify FALSE, you can see commas.

Example:
=FIXED(123456.5566,2,TRUE) would return 123456.66
=FIXED(123456.5566,2,FALSE) would return 123,456.56

FIND:

Find function has 3 arguments (Find_Text, Within_Text, Start_Num). Find Function is case sensitive.

Find_Text(First Argument): You have to mention the TEXT that you are searching.

Within_Text(Second Argument). You have to mention the TEXT on which you are searching.

Start_Num(Third argument): You have to specify the number of that character from which you want to search.

.

Example:
=FIND("for", "Information Technology",2) would return 3 since for starts from the third character
=FIND("nolo","Information Technology",1) would return 17 since nolo starts from the 17th character.
=FIND("NOLO","Information Technology",1) would not return anything since NOLO is in upper case and you have nolo in the text.

SEARCH:

Search function is similar like find function but it is not case sensitive. It can accept wild cards also.

REPLACE:

Replace function has 4 arguments (Old_Text, Start_Num, Num_Chars_New_Text).

Old_Text (First Argument): you have to mention the text which has to be replaced.

Start_Num(Second Argument): You have to specify the number of that character from which you want to replace.

Num_Chars (Third Argument): The number of characters to be replaced in Old_Text(First Argument)

New_Text (Fourth Argument): The new text that will be replaced in Old_Text(First Argument)

Example:
=REPLACE("Information Technology",1,3,"AAA") would return AAAormation Technology.

CONCATENATE:

You have to refer the Excel_Exercise diagram:


Exercise - Excel 2013
Exercise
  • If you need to concatenate first name and last name as Full name in another column, do the following.
  • Under column M, in P4, type the header as Full Name. Place the cursor in P5. Type = in that cell. Whenever you type = in any one of the cell, you can see several functions in the drop down list box above the column A. Click concatenate function. A new box is opened, which has two boxes Text1 and Text2. Place the cursor on text1 box. Click B5 cell and now in text1 box, you can see B5. Similarly place the cursor on text2 box. Click C5 cell and now in text2 box, you can see C5. On the right side of the Text1 box, you can see John and in Text2 box, you can see Smith. CLICK OK. You can see full name as JohnSmith on the P6 cell.
  • If you want to copy the formula for other cells, place the cursor on P6 cell. On the right bottom corner of the cell, when you move the cursor, the big plus symbol changes to a small plus symbol. Press the left button of the mouse and drag it until the P9 cell. You can see names concatenated from P6 … P9.



Previous  Lookup Functions in Excel 2013     |     Convert TEXT Values into Columns  Next