Text Functions

Calc spreadsheet file used in video: Right-click to download file

The TEXT Function

The TEXT function can be used to format a number in a cell to show significant and insignificant digits and thousands separator.
The text function takes 2 parameters:

• number: a number to be formatted.
• Format: the format to be applied to the number use # to display significant digits, 0 to display insignificant digits, and #,### to indicate a comma as thousands separator every 3 significant digits. The format must be enclosed in double quotes.

An example of the TEXT function:

the number 45556234.23 was referenced in the number parameter of the text function. the format #,### indicated that a comma every 3 digits should display, the .0000 indicated that 4 insignificant digits should be displayed after the decimal point.

Using text in a formula

When using text in a formula the text must be enclosed in double-quotes. the text is joined with the results of a function or a cell reference by the & (ampersand) character:

LEFT Function

The LEFT function will extract characters from the beginning a text string. the parameters are:

• text: the text string.
• number: the number of characters from the left to extract.

MID Function

The MID function will extract characters from the middle of a text string. There are three parameters:

• text: the text string.
• start: the position of the starting character, counting from 1.
• number: the number of characters to extract.

RIGHT Function

The RIGHT function will extract characters from the end of a text string. There are 2 parameters:

• text: the text string.
• number: the number of characters from the right to extract.

REPLACE Function

The REPLACE function will replace a section of text in a text string with another text string. There are 4 parameters:

• Text: the text string.
• position: The character position where the replacement will begin, starting from 1.
• length: the number of characters to replace.
• new text: the new text string replacing the old text. Must be enclosed in double quotes.

In the following example the numbers 5 and 6 are replaced with the text NEW:

SUBSTITUDE function

The SUBSTITUDE function will substitude any or all occurrences of text within a text string.
There are 4 parameters:

• Text: the text string.
• search_text: the text being searched for within the text string.
• new text: the new text to substitude for the text being searched.
• occurrence: (optional) the occurrance of the text to be substituded, if the parameter is left out, all occurances will be substituded.

In the following example the letter X substitudes the letters BC in the second occurrance only:

In the next example all occurances of the letters BC are substituded with X:

Text to Columns

The Text to columns Feature in Calc can import a text strings from a column and separated parts of the text into separate columns. If the text strings are different widths the text can be separated by a Tab, comma, semicolon, space and other characters. For example the following column of references numbers can be separated into columns by the - (minus sign or dash) character and a space. first select the column of text:

Then to bring up the Text to Columns dialog go to the Data menu and choose Text to Columns.

Under Separator Options make sure Space and Other are checked, then type a - (minus sign or dash) in the Other textbox.
Under Fields, a preview will appear of the columns.
Press OK

For text with a fixed width which has no separators The text to columns can also be used to separate different parts of the text. First select the text:

Bring up the Text to Columns dialog.
Under Separator Options choose Fixed Width:

Under Fields hover the mouse pointer over the Ruler at the top and a Black line will appear, move the line to the location where the separation should occur then click. A red dot will appear on the ruler where the text will be separated. Repeat the process for more separations. To delete a separation hover the mouse pointer over it and click.

The result: