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,"$#,#####") |