1. Zorba 3.0 Documentation
  2. Modules
  3. Zorba
  4. Excel
  5. Text

http://zorba.io/modules/excel/text

View as XML or JSON.

This is a library module offering the same set of functions defined by Microsoft Excel, under Text and Data Functions.

Function Summary

asc ($text as xs:string) as xs:string

Returns the given $text unchanged.
char ($number as xs:integer) as xs:string

Returns the character specified by a certain codepoint.
clean ($arg as xs:string?) as xs:string?

Removes all nonprintable characters from text.
code ($arg as xs:string) as xs:integer

Returns a codepoint for the first character in a text string.
concatenate ($args as xs:anyAtomicType*) as xs:string

Joins several text strings into one text string.
concatenate ($arg1 as xs:anyAtomicType?, $arg2 as xs:anyAtomicType?) as xs:string

Joins two text strings into one text string.
dollar ($number as xs:decimal) as xs:string

Converts a number to text format and applies a currency symbol.
dollar ($number as xs:decimal, $decimals as xs:decimal) as xs:string

Converts a number to text format and applies a currency symbol.
exact ($arg1 as xs:string, $arg2 as xs:string) as xs:boolean

Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise.
find ($find_text as xs:string, $within_text as xs:string) as xs:integer?

Locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.
find ($find_text as xs:string, $within_text as xs:string, $start_num as xs:integer) as xs:integer?

Locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.
fixed ($number as xs:decimal, $decimals as xs:decimal) as xs:string

Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.
fixed ($number as xs:decimal, $decimals as xs:decimal, $no_commas as xs:boolean) as xs:string

Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.
left ($arg as xs:string) as xs:string

Returns the first character in a text string.
left ($text as xs:string, $num_chars as xs:integer) as xs:string

Returns the first character or characters in $text, based on the number of $num_chars you specify.
len ($arg as xs:string?) as xs:integer

Returns the number of characters in a text string.
lower ($arg as xs:string?) as xs:string?

Converts all uppercase letters in a text string to lowercase.
mid ($text as xs:string?, $start_num as xs:integer, $num_chars as xs:integer) as xs:string?

Returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.
pad-integer-to-length ($toPad as xs:anyAtomicType?, $padChar as xs:string, $length as xs:integer) as xs:string

Returns $toPad appended with enough repetitions of $padChar to make its length $length, the characters are added before the string.
replace ($old_text as xs:string?, $start_num as xs:integer, $num_chars as xs:integer, $new_text as xs:string) as xs:string

Replaces part of a text string, based on the number of characters you specify, with a different text string.
right ($arg as xs:string) as xs:string

Returns the last character in a text string.
right ($text as xs:string, $num_chars as xs:integer) as xs:string

Returns the last character or characters in a text string, based on the number of characters you specify.
search ($find_text as xs:string, $within_text as xs:string) as xs:integer?

Locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.
search ($find_text as xs:string, $within_text as xs:string, $start_num as xs:integer) as xs:integer?

Locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.
substitute ($text as xs:string, $old_text as xs:string, $new_text as xs:string) as xs:string?

Substitutes new_text for old_text in a text string.
substitute ($text as xs:string, $old_text as xs:string, $new_text as xs:string, $instance_num as xs:integer?) as xs:string

Substitutes new_text for old_text in a text string.
t ($value as xs:anyAtomicType?) as xs:string

Converts the $value to string.
trim ($text as xs:string?) as xs:string?

Removes all spaces from text except for single spaces between words.
upper ($text as xs:string?) as xs:string?

Converts text to uppercase.
value-except ($arg1 as xs:anyAtomicType*, $arg2 as xs:anyAtomicType*) as xs:anyAtomicType*

Returns the values in one sequence that do not appear in the second sequence in an implementation-defined order.
value ($arg as xs:anyAtomicType?) as xs:anyAtomicType?

Converts a text string that represents a number to a number.

Functions

asc#1

declare  function excel-text:asc($text as xs:string) as xs:string
Returns the given $text unchanged.

Parameters

text as xs:string
the time

Returns

xs:string
The given $text unchanged.

char#1

declare  function excel-text:char($number as xs:integer) as xs:string
Returns the character specified by a certain codepoint.

Parameters

number as xs:integer
the codepoint.

Returns

xs:string
the character specified by a certain codepoint.

clean#1

declare  function excel-text:clean($arg as xs:string?) as xs:string?
Removes all nonprintable characters from text.

Parameters

arg as xs:string
the string.

Returns

xs:string?
Removes all nonprintable characters from text. The CLEAN function was designed. to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text.

In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157).

By itself, the CLEAN function does not remove these additional nonprinting characters.

code#1

declare  function excel-text:code($arg as xs:string) as xs:integer
Returns a codepoint for the first character in a text string.

Parameters

arg as xs:string
the string.

Returns

xs:integer
A codepoint for the first character in a text string.

concatenate#1

declare  function excel-text:concatenate($args as xs:anyAtomicType*) as xs:string
Joins several text strings into one text string.

Parameters

args as xs:anyAtomicType
a sequence of strings.

Returns

xs:string
Joins several text strings into one text string.

concatenate#2

declare  function excel-text:concatenate($arg1 as xs:anyAtomicType?, $arg2 as xs:anyAtomicType?) as xs:string
Joins two text strings into one text string.

Parameters

arg1 as xs:anyAtomicType
the first string.
arg2 as xs:anyAtomicType
the second string.

Returns

xs:string
Joins two text strings into one text string.

dollar#1

declare  function excel-text:dollar($number as xs:decimal) as xs:string
Converts a number to text format and applies a currency symbol. The number of digits to the right of the decimal point is 2.

Parameters

number as xs:decimal
is the number.

Returns

xs:string
Converts a number to text format and applies a currency symbol. The number of digits to the right of the decimal point is 2.

dollar#2

declare  function excel-text:dollar($number as xs:decimal, $decimals as xs:decimal) as xs:string
Converts a number to text format and applies a currency symbol.

Parameters

number as xs:decimal
is the number.
decimals as xs:decimal
is the number of digits to the right of the decimal point.

If decimals is negative, number is rounded to the left of the decimal point.

Returns

xs:string
Converts a number to text format and applies a currency symbol.

exact#2

declare  function excel-text:exact($arg1 as xs:string, $arg2 as xs:string) as xs:boolean
Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences.

Parameters

arg1 as xs:string
the first string.
arg2 as xs:string
the second string.

Returns

xs:boolean
Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences.

find#2

declare  function excel-text:find($find_text as xs:string, $within_text as xs:string) as xs:integer?
Locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

The search is case sensitive.

Parameters

find_text as xs:string
text you want to find.
within_text as xs:string
text in which you want to search for $find_text.

Returns

xs:integer?
Locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

The search is case sensitive.

find#3

declare  function excel-text:find($find_text as xs:string, $within_text as xs:string, $start_num as xs:integer) as xs:integer?
Locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

The search is case sensitive.

Parameters

find_text as xs:string
text you want to find.
within_text as xs:string
text in which you want to search for $find_text.
start_num as xs:integer
specifies the character at which to start the search.

Returns

xs:integer?
Locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

The search is case sensitive.

fixed#2

declare  function excel-text:fixed($number as xs:decimal, $decimals as xs:decimal) as xs:string
Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.

Parameters

number as xs:decimal
is the number you want to round and convert to text.
decimals as xs:decimal
is the number of digits to the right of the decimal point.

Returns

xs:string
Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.

fixed#3

declare  function excel-text:fixed($number as xs:decimal, $decimals as xs:decimal, $no_commas as xs:boolean) as xs:string
Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.

Parameters

number as xs:decimal
is the number you want to round and convert to text.
decimals as xs:decimal
is the number of digits to the right of the decimal point.
no_commas as xs:boolean
is a logical value that, if TRUE, prevents FIXED from including commas in the returned text.

Returns

xs:string
Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.

left#1

declare  function excel-text:left($arg as xs:string) as xs:string
Returns the first character in a text string.

Parameters

arg as xs:string
is the text string that contains the characters you want to extract.

Returns

xs:string
The first character in a text string.

left#2

declare  function excel-text:left($text as xs:string, $num_chars as xs:integer) as xs:string
Returns the first character or characters in $text, based on the number of $num_chars you specify.

Parameters

text as xs:string
is the text string that contains the characters you want to extract.
num_chars as xs:integer
specifies the number of characters you want to extract.

Returns

xs:string
The first character or characters in $text, based on the number of $num_chars you specify.

len#1

declare  function excel-text:len($arg as xs:string?) as xs:integer
Returns the number of characters in a text string.

Parameters

arg as xs:string
the string.

Returns

xs:integer
The number of characters in a text string.

lower#1

declare  function excel-text:lower($arg as xs:string?) as xs:string?
Converts all uppercase letters in a text string to lowercase.

Parameters

arg as xs:string
the string.

Returns

xs:string?
Converts all uppercase letters in a text string to lowercase.

mid#3

declare  function excel-text:mid($text as xs:string?, $start_num as xs:integer, $num_chars as xs:integer) as xs:string?
Returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

Parameters

text as xs:string
the text string containing the characters you want to extract.
start_num as xs:integer
the position of the first character you want to extract in text. The first character in text has start_num 1, and so on.
num_chars as xs:integer
the number of characters you want to return from text.

Returns

xs:string?
A specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

pad-integer-to-length#3

declare  function excel-text:pad-integer-to-length($toPad as xs:anyAtomicType?, $padChar as xs:string, $length as xs:integer) as xs:string
Returns $toPad appended with enough repetitions of $padChar to make its length $length, the characters are added before the string.

Parameters

toPad as xs:anyAtomicType
the value to be padded.
padChar as xs:string
the character used for padding.
length as xs:integer
the desired length.

Returns

xs:string
$toPad appended with enough repetitions of $padChar to make its length $length, the characters are added before the string.

replace#4

declare  function excel-text:replace($old_text as xs:string?, $start_num as xs:integer, $num_chars as xs:integer, $new_text as xs:string) as xs:string
Replaces part of a text string, based on the number of characters you specify, with a different text string.

Parameters

old_text as xs:string
is text in which you want to replace some characters.
start_num as xs:integer
the position of the character in old_text that you want to replace with new_text.
num_chars as xs:integer
the number of characters in old_text that you want REPLACE to replace with new_text.
new_text as xs:string
the text that will replace characters in old_text.

Returns

xs:string
Replaces part of a text string, based on the number of characters you specify, with a different text string.

right#1

declare  function excel-text:right($arg as xs:string) as xs:string
Returns the last character in a text string.

Parameters

arg as xs:string
the text string containing the characters you want to extract.

Returns

xs:string
The last character in a text string.

right#2

declare  function excel-text:right($text as xs:string, $num_chars as xs:integer) as xs:string
Returns the last character or characters in a text string, based on the number of characters you specify.

Parameters

text as xs:string
the text string containing the characters you want to extract.
num_chars as xs:integer
specifies the number of characters you want RIGHT to extract.

Returns

xs:string
The last character or characters in a text string, based on the number of characters you specify.

search#2

declare  function excel-text:search($find_text as xs:string, $within_text as xs:string) as xs:integer?
Locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

The search starts at position 1, and it is not case sensitive.

Parameters

find_text as xs:string
text you want to find.
within_text as xs:string
text in which you want to search for $find_text.

Returns

xs:integer?
Locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

The search starts at position 1, and it is not case sensitive.

search#3

declare  function excel-text:search($find_text as xs:string, $within_text as xs:string, $start_num as xs:integer) as xs:integer?
Locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

The search starts at $start_num, and it is not case sensitive.

Parameters

find_text as xs:string
text you want to find.
within_text as xs:string
text in which you want to search for $find_text.
start_num as xs:integer
the character number in within_text at which you want to start searching.

Returns

xs:integer?
Locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

The search starts at $start_num, and it is not case sensitive.

substitute#3

declare  function excel-text:substitute($text as xs:string, $old_text as xs:string, $new_text as xs:string) as xs:string?
Substitutes new_text for old_text in a text string. Every occurrence of old_text in text is changed to new_text.

Parameters

text as xs:string
the text or the reference to a cell containing text for which you want to substitute characters.
old_text as xs:string
text you want to replace.
new_text as xs:string
text you want to replace old_text with.

Returns

xs:string?
Substitutes new_text for old_text in a text string. Every occurrence of old_text in text is changed to new_text.

substitute#4

declare  function excel-text:substitute($text as xs:string, $old_text as xs:string, $new_text as xs:string, $instance_num as xs:integer?) as xs:string
Substitutes new_text for old_text in a text string.

Parameters

text as xs:string
the text or the reference to a cell containing text for which you want to substitute characters.
old_text as xs:string
text you want to replace.
new_text as xs:string
text you want to replace old_text with.
instance_num as xs:integer
specifies which occurrence of old_text you want to replace with new_text.

Only that instance of old_text is replaced.

Returns

xs:string
Substitutes new_text for old_text in a text string.

Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.

t#1

declare  function excel-text:t($value as xs:anyAtomicType?) as xs:string
Converts the $value to string.

Parameters

value as xs:anyAtomicType
the value

Returns

xs:string
Converts the $value to string.

trim#1

declare  function excel-text:trim($text as xs:string?) as xs:string?
Removes all spaces from text except for single spaces between words.

Parameters

text as xs:string
from which you want spaces removed.

Returns

xs:string?
Removes all spaces from text except for single spaces between words.

upper#1

declare  function excel-text:upper($text as xs:string?) as xs:string?
Converts text to uppercase.

Parameters

text as xs:string
text you want converted to uppercase.

Returns

xs:string?
Converts text to uppercase.

value-except#2

declare  function excel-text:value-except($arg1 as xs:anyAtomicType*, $arg2 as xs:anyAtomicType*) as xs:anyAtomicType*
Returns the values in one sequence that do not appear in the second sequence in an implementation-defined order.

Parameters

arg1 as xs:anyAtomicType
the first sequence.
arg2 as xs:anyAtomicType
the second sequence.

Returns

xs:anyAtomicType*
The values in one sequence that do not appear in the second sequence in an implementation-defined order.

value#1

declare  function excel-text:value($arg as xs:anyAtomicType?) as xs:anyAtomicType?
Converts a text string that represents a number to a number.

Parameters

arg as xs:anyAtomicType
the value.

Returns

xs:anyAtomicType?
Converts a text string that represents a number to a number.