http://www.zorba-xquery.com/modules/excel/text

import module namespace excel-text = "http://www.zorba-xquery.com/modules/excel/text";

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

Sorin Nasoi

xquery version "3.0" encoding "utf-8";

• the XQuery module can be found here.

Imported modules:

• http://www.zorba-xquery.com/modules/excel/math

For more details please also see:

• Excel Documentation: Text Functions

asc

declare function excel-text:asc (
            $text as xs:string 
 ) as xs:string

Returns the given $text unchanged.

• $text the time

• The given $text unchanged.

• http://office.microsoft.com/en-us/excel/HP052508361033.aspx

char

declare function excel-text:char (
            $number as xs:integer 
 ) as xs:string

Returns the character specified by a certain codepoint.

• $number the codepoint.

• the character specified by a certain codepoint.

• excel-err:Value provided $number must be in range [1,255].

• http://office.microsoft.com/en-us/excel/HP052090091033.aspx

clean

declare function excel-text:clean (
            $arg as xs:string? 
 ) as xs:string?

Removes all nonprintable characters from text.

• $arg the 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.

• http://office.microsoft.com/en-us/excel/HP052090141033.aspx

code

declare function excel-text:code (
            $arg as xs:string 
 ) as xs:integer

Returns a codepoint for the first character in a text string.

• $arg the string.

• A codepoint for the first character in a text string.

• excel-err:Value Provided $arg was empty.

• http://office.microsoft.com/en-us/excel/HP052090151033.aspx

concatenate

declare function excel-text:concatenate (
            $args as xs:anyAtomicType* 
 ) as xs:string

Joins several text strings into one text string.

• $args a sequence of strings.

• Joins several text strings into one text string.

• http://office.microsoft.com/en-us/excel/HP052090151033.aspx

concatenate

declare function excel-text:concatenate (
            $arg1 as xs:anyAtomicType?, 
            $arg2 as xs:anyAtomicType? 
 ) as xs:string

Joins two text strings into one text string.

• $arg1 the first string.
• $arg2 the second string.

• Joins two text strings into one text string.

• http://office.microsoft.com/en-us/excel/HP052090151033.aspx

dollar

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.

• $number is the number.

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

• http://office.microsoft.com/en-us/excel/HP052090931033.aspx

dollar

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.

• $number is the number.
• $decimals 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.

• Converts a number to text format and applies a currency symbol.

• http://office.microsoft.com/en-us/excel/HP052090931033.aspx

exact

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.

• $arg1 the first string.
• $arg2 the second string.

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

• http://office.microsoft.com/en-us/excel/HP052090811033.aspx

find

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.

• $find_text text you want to find.
• $within_text text in which you want to search for $find_text.

• 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.

• excel-err:Value the value is not greater than zero or is greater than the length of within_text.
• excel-err:Value value was not found.

• http://office.microsoft.com/en-us/excel/HP052090891033.aspx

find

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.

• $find_text text you want to find.
• $within_text text in which you want to search for $find_text.
• $start_num specifies the character at which to start the search.

• 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.

• excel-err:Value the value is not greater than zero or is greater than the length of within_text.
• excel-err:Value value was not found.

• http://office.microsoft.com/en-us/excel/HP052090891033.aspx

fixed

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.

• $number is the number you want to round and convert to text.
• $decimals is the number of digits to the right of the decimal point.

• 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.

• http://office.microsoft.com/en-us/excel/HP052090931033.aspx

fixed

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.

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

• 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.

• http://office.microsoft.com/en-us/excel/HP052090931033.aspx

left

declare function excel-text:left (
            $arg as xs:string 
 ) as xs:string

Returns the first character in a text string.

• $arg is the text string that contains the characters you want to extract.

• The first character in a text string.

• http://office.microsoft.com/en-us/excel/HP052091531033.aspx

left

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.

• $text is the text string that contains the characters you want to extract.
• $num_chars specifies the number of characters you want to extract.

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

• http://office.microsoft.com/en-us/excel/HP052091531033.aspx

len

declare function excel-text:len (
            $arg as xs:string? 
 ) as xs:integer

Returns the number of characters in a text string.

• $arg the string.

• The number of characters in a text string.

• http://office.microsoft.com/en-us/excel/HP052091541033.aspx

lower

declare function excel-text:lower (
            $arg as xs:string? 
 ) as xs:string?

Converts all uppercase letters in a text string to lowercase.

• $arg the string.

• Converts all uppercase letters in a text string to lowercase.

• http://office.microsoft.com/en-us/excel/HP052091671033.aspx

mid

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.

• $text the text string containing the characters you want to extract.
• $start_num 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 the number of characters you want to return from text.

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

• http://office.microsoft.com/en-us/excel/HP052091751033.aspx

pad-integer-to-length

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.

• $toPad the value to be padded.
• $padChar the character used for padding.
• $length the desired length.

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

• excel-err:Value if the length of the $toPad is greater than the desired length.

replace

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.

• $old_text is text in which you want to replace some characters.
• $start_num the position of the character in old_text that you want to replace with new_text.
• $num_chars the number of characters in old_text that you want REPLACE to replace with new_text.
• $new_text the text that will replace characters in old_text.

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

• http://office.microsoft.com/en-us/excel/HP052092351033.aspx

right

declare function excel-text:right (
            $arg as xs:string 
 ) as xs:string

Returns the last character in a text string.

• $arg the text string containing the characters you want to extract.

• The last character in a text string.

• http://office.microsoft.com/en-us/excel/HP052092371033.aspx

right

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.

• $text the text string containing the characters you want to extract.
• $num_chars specifies the number of characters you want RIGHT to extract.

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

• http://office.microsoft.com/en-us/excel/HP052092371033.aspx

search

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.

• $find_text text you want to find.
• $within_text text in which you want to search for $find_text.

• 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.

• excel-err:Value the value is not greater than zero or is greater than the length of within_text.
• excel-err:Value value was not found.

• http://office.microsoft.com/en-us/excel/HP052092491033.aspx

search

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.

• $find_text text you want to find.
• $within_text text in which you want to search for $find_text.
• $start_num the character number in within_text at which you want to start searching.

• 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.

• excel-err:Value the value is not greater than zero or is greater than the length of within_text.
• excel-err:Value value was not found.

• http://office.microsoft.com/en-us/excel/HP052092491033.aspx

substitute

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.

• $text the text or the reference to a cell containing text for which you want to substitute characters.
• $old_text text you want to replace.
• $new_text text you want to replace old_text with.

• Substitutes new_text for old_text in a text string. Every occurrence of old_text in text is changed to new_text.

• http://office.microsoft.com/en-us/excel/HP052092861033.aspx

substitute

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.

• $text the text or the reference to a cell containing text for which you want to substitute characters.
• $old_text text you want to replace.
• $new_text text you want to replace old_text with.
• $instance_num specifies which occurrence of old_text you want to replace with new_text. Only that instance of old_text is replaced.

• 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.

• http://office.microsoft.com/en-us/excel/HP052092861033.aspx

t

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

Converts the $value to string.

• $value the value

• Converts the $value to string.

• http://office.microsoft.com/en-us/excel/HP052093041033.aspx

trim

declare function excel-text:trim (
            $text as xs:string? 
 ) as xs:string?

Removes all spaces from text except for single spaces between words.

• $text from which you want spaces removed.

• Removes all spaces from text except for single spaces between words.

• http://office.microsoft.com/en-us/excel/HP052093211033.aspx?pid=CH062528321033

upper

declare function excel-text:upper (
            $text as xs:string? 
 ) as xs:string?

Converts text to uppercase.

• $text text you want converted to uppercase.

• Converts text to uppercase.

• http://office.microsoft.com/en-us/excel/HP052093271033.aspx

value

declare function excel-text:value (
            $arg as xs:anyAtomicType? 
 ) as xs:anyAtomicType?

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

• $arg the value.

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

• excel-err:Value provided value is not a number.

• http://office.microsoft.com/en-us/excel/HP052093291033.aspx

value-except

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.

• $arg1 the first sequence.
• $arg2 the second sequence.

• The values in one sequence that do not appear in the second sequence in an implementation-defined order.