IBM Lotus Symphony
|
This section contains descriptions of the Text functions, together with an example. The functions described below are: ARABIC , BASE , CHAR , CLEAN , CODE , CONCATENATE , DECIMAL , DOLLAR , FIND , FIXED , LEFT , LEN , LOWER , MID , PROPER , REPLACE , REPT , RIGHT , ROMAN , SEARCH , SUBSTITUTE , T , TEXT , TRIM , UPPER , VALUE .
Converts a positive integer to a specified base into a text from the numbering system . The digits 0-9 and the letters A-Z are used.
BASE(Number; Radix; [Minimum length])
number is the positive integer to be converted.
radix indicates the base of the number system. It may be any positive integer between 2 and 36.
Minimum length (optional) determines the minimum length of the character sequence that has been created. If the text is shorter than the indicated minimum length, zeros are added to the left of the string.
BASE(17;10;4) returns 0017 in the decimal system.
BASE(17;2) returns 10001 in the binary system.
BASE(255;16;4) returns 00FF in the hexadecimal system.
See also the following functions:
DECIMAL .
Converts a number into a character according to the current code table. The number can be a two-digit or three-digit integer number.
Returns a numeric code for the first character in a text string.
Combines several text strings into one string.
Converts text with characters from a number system to a positive integer in the base radix given. The radix must be in the range 2 to 36. Spaces and tabs are ignored. The text field is not case-sensitive.
If the radix is 16, a leading x or X or 0x or 0X, and an appended h or H, is disregarded. If the radix is 2, an appended b or B is disregarded. Other characters that do not belong to the number system generate an error.
DECIMAL(Text; Radix)
text is the text to be converted. To differentiate between a hexadecimal number, such as A1 and the reference to cell A1, you must to place the number in quotation marks, for example, "A1" or "AFFE".
radix indicates the base of the number system. It may be any positive integer between 2 and 36.
DECIMAL("17";10) returns 17.
DECIMAL("FACE";16) returns 64206.
DECIMAL("0101";2) returns 5.
See also the following functions:
BASE .
Converts a number to an amount in the currency format, rounded to a specified decimal place. In the value field enter the number to be converted to currency. Optionally, you may enter the number of decimal places in the decimals field. If no value is specified, all numbers in currency format will be displayed with two decimal places.
You set the currency format in your system settings.
DOLLAR(value; decimals)
value is a number, a reference to a cell containing a number, or a formula which returns a number.
decimals is the number of decimal places.
DOLLAR(255) returns $255.00.
DOLLAR(367.456;2) returns $367.46. Use the decimal separator that corresponds to the current locale setting .
See also the following functions:
Compares two text strings and returns TRUE if they are identical. This function is case-sensitive.
Looks for a string of text within another string. You can also define where to begin the search. The search term can be a number or any string of characters. The search is case-sensitive.
Specifies that a number be displayed with a fixed number of decimal places and with or without a thousands separator. This function can be used to apply a uniform format to a column of numbers.
FIXED(Number; decimals; no thousands separators)
Number refers to the number to be formatted.
Decimals refers to the number of decimal places to be displayed.
No thousands separators (optional) determines whether the thousands separator is used. If the parameter is a number not equal to 0, the thousands separator is suppressed. If the parameter is equal to 0 or if it is missing altogether, the thousands separators of your current locale setting are displayed.
Returns the first character or characters in a text string.
Returns a text segment of a character string. The parameters specify the starting position and the number of characters.
Replaces part of a text string with a different text string. This function can be used to replace both characters and numbers (which are automatically converted to text). The result of the function is always displayed as text. If you intend to perform further calculations with a number which has been replaced by text, you will need to convert it back to a number using the VALUE function.
Any text containing numbers must be enclosed in quotation marks if you do not want it to be interpreted as a number and automatically converted to text.
REPLACE(text; position; length; new text)
text refers to text of which a part will be replaced.
position refers to the position within the text where the replacement will begin.
length is the number of characters in text to be replaced.
new text refers to the text which replaces text .
REPLACE("1234567";1;1;"444") returns "444234567". One character at position 1 is replaced by the complete new text .
See also the following functions:
MID , SEARCH , SUBSTITUTE , TRIM .
Repeats a character string by the given number of copies.
Defines the last character or characters in a text string.
Converts a number into a Roman numeral. The value range must be between 0 and 3999, the modes can be integers from 0 to 4.
ROMAN(Number; Mode)
Number is the number that is to be converted into a Roman numeral.
Mode (optional) indicates the degree of simplification. The higher the value, the greater is the simplification of the Roman number.
Returns the position of a text segment within a character string. You can set the start of the search as an option. The search text can be a number or any sequence of characters. The search is not case-sensitive.
The search supports
. As soon as you have entered text, you can enter "all.*", for example to find the first location of "all" followed by any characters.
SEARCH(find_text; text; position)
find_text is the text to be searched for.
text is the text where the search will take place.
position (optional) is the position in the text where the search is to start.
SEARCH(54;998877665544) returns 10.
See also the following functions:
FIND , MID , REPLACE , SUBSTITUTE .
Substitutes new text for old text in a string.
SUBSTITUTE(text; search_text; new text; occurrence)
text is the text in which text segments are to be exchanged.
search_text is the text segment that is to be replaced (a number of times).
new text is the text that is to replace the text segment.
occurrence (optional) indicates how many occurrences of the search text are to be replaced. If this parameter is missing the search text is replaced throughout.
This function converts a number to a blank text string.
Removes spaces that are in front of a string, or aligns cell contents to the left.
TRIM(Text)
text refers to text in which leading spaces are removed, or to the cell in which the contents will be left-aligned.
TRIM(" hello") returns "hello".
See also the following functions:
CLEAN , REPLACE , MID , SUBSTITUTE .