Excel Tips and Tricks- Text Functions

March 12, 2010

Hoor, our Bangalore Excel trainer, shares some little known text functions with you

FIXED Function:

The FIXED function rounds a value to specified precision and then converts the rounded value to text. The function uses the following syntax:

=FIXED (number, decimals, no _commas)

The number argument supplies the value that you want to round and convert to text. The optional decimals argument tells Excel how many places to the right of the decimal point that you want to round. The optional no_commas argument needs to be either 1 (if you want commas) or 0 (if you don’t want commas) in the returned text.

For example, to round to a whole number and convert to text the value 1234.56789, use the following formula:

=FIXED (1234.56789,0,1)

The function returns the text 1,235.

REPT function:

The REPT function repeats a text string. The function uses the following syntax:

=REPT (text, number _ times)

The text argument either supplies the text string or references the cell holding the text string. The number_times argument tells Excel how many times you want to repeat the text. For example, the following formula:

=REPT(“Bora”,2”)

Returns the text string BoraBora.

VALUE function

The VALUE function converts a text string that looks like a value to a value.

The function uses the following syntax:

=VALUE (text)

The text argument either supplies the text string that you want to convert or it references the cell holding the text string. For example, to convert the text string $123,456.78 — assume that this isn’t a value but a text string you can use the following formula:

=VALUE (“$123,456.78”)

The function returns the value 123456.78.

Advertisements
%d bloggers like this: