Excel Text functions: exact, fixed, proper, rept, text

Excel Function EXACT

The EXACT() function is used to determine if the value of two texts are identical.

This function returns a Boolean value of TRUE or FALSE based on the comparison.

TRUE is returned if the texts are identical, else, FALSE is returned.

This function is case sensitive.

Text 1 Text 2 Formula EXACT()
Bird Bird =EXACT(L4,M4) True
a A =EXACT(L5,M5) False
boy BOY =EXACT(L6,M6) False
WOMAN wom =EXACT(L7,M7) False
lady lady =EXACT(L8,M8) True

 

 

Excel Function FIXED

The FIXED() function is used to round a specified number to a fixed number of decimal places.

Though it deals with numbers, it is classified as a text function since the result is expressed as a text.

This function takes an optional parameter, which specifies whether the comma separation is to be used.

Number FIXED() FORMULA
23 23.00 =FIXED(L17,2)
34556 34556.00 =FIXED(L18,2,TRUE)
100 100.00 =FIXED(L19,2,TRUE)
2000 2'000.00 =FIXED(L20,2,FALSE)
100456 100'456.00 =FIXED(L21,2,FALSE)

 

 

Excel Function PROPER

The proper function is used to convert first character of every word in a sentence to upper case.

All characters that do not immediately follow an alphabet are converted to upper case characters.

Even if a character occurs immediately after a number or a symbol, that character is also converted to upper case.

Text FIXED() FORMULA
I am sam I Am Sam =PROPER(L29)
I am sam's brother I Am Sam'S Brother =PROPER(L30)
the book is mine. The Book Is Mine. =PROPER(L31)
THIS IS MY PLACE. This Is My Place. =PROPER(L32)
My PassWord is 123abcd My Password Is 123Abcd =PROPER(L33)

Note the last sentence. The function has converted the 'a' to 'A'. This is not the desired result.

Hence, this function has its drawbacks and should be used cautiously.

 

 

Excel Function REPT

The REPT() function is used to repeat a given string for the specified number of times.

This function repeats the string in the same cell and is contiguous.

The maximum number of times a string can be repeated is restricted to a maximum of 32767 chars in all.

That is, a cell can accommodate a maximum of 32K chars and the maximum repeat value depends on length of

string.

A repeat value of 0, results in an empty text.

Text REPT() FORMULA
ha hahahaha =REPT(L43,4)
hello anderson hello andersonhello anderson =REPT(L44,2)
* * =REPT(L45,1)
# =REPT(L46,0)
Microsoftexcel #VALUE! =REPT(L47,30000)

 

 

Excel Function TEXT()

The TEXT() function is used to convert a supplied number into text format.

The format to be used with the result is also specified in the function.

This is similar to FIXED() function except that, this does not round the number.

Number TEXT() FORMULA
42165 10/06/2015 =TEXT(L58,"dd/mm/yyyy")
0.656516203703704 15:45 =TEXT(L59,"hh:mm")
0.656516203703704 45:23 =TEXT(L60,"mm:ss")
2000 $2000 =TEXT(L61,"$####")
100456 $1,00456 =TEXT(L62,"$#,#####")

 

You can find similar Excel Questions and Answer hereunder

1) Concatenate number with text but keeping in number format in Excel

2) How can I get the most frequently occurring text in a range?

3) text functions asc clean char code dollar

4) Here an explanation about text box and how to control text boxes in Excel VBA

5) text functions left right t

6) Can I add a Text histogram to Table data to make it visually appealing and easy to interpret?

7) How can I enter a text in a cell with subscript and superscript?

8) Vba delete entire row if contains certain text in Excel

9) Write to text file without quotes in vba in Excel

10) How can I get row count of filtered data?

 

Here the previous and next chapter