Apache OpenOffice Wiki - User contributions [en]

Documentation/DevGuide/Spreadsheets/Spreadsheet Documents

2013-07-21T15:27:21Z

GreatBigBore: Subject/verb agreement

{{Documentation/DevGuide/SpreadsheetsTOC
|SpreadsheetDocs2a=block
|ShowPrevNext=block
|PrevPage=Documentation/DevGuide/Text/TextViewCursor
|NextPage=Documentation/DevGuide/Spreadsheets/Example: Adding a New Spreadsheet
}}
{{Documentation/DevGuideLanguages|Documentation/DevGuide/Spreadsheets/{{SUBPAGENAME}}}}
{{DISPLAYTITLE:Spreadsheet Documents}}
{{Download Collection|MediaWiki:Collections/Developer's Guide - Spreadsheet Documents}}

{{PRODUCTNAME}} API knows three variants of tables: text tables (see [[Documentation/DevGuide/Text/Tables|Tables]]), database tables (see [[Documentation/DevGuide/Database/Table Service|Table Service]]) and spreadsheets. Each table concept has its own purpose. Text tables handle text contents, database tables offer database functionality and spreadsheets operate on data cells that can be evaluated. Being specialized in such a way means that each concept has its strength. Text tables offer full functionality for text formatting, where spreadsheets support complex calculations. Alternately, spreadsheets support only basic formatting capabilities and text tables perform elementary calculations.

The implementations of the various tables differ due to each of their specializations. Basic table features are defined in the module <idlmodule>com.sun.star.table</idlmodule>. Regarding the compatibility of text and spreadsheet tables, the corresponding features are also located in the module <idlmodule>com.sun.star.table</idlmodule>. In addition, spreadsheet tables are fully based on the specifications given and are extended by additional specifications from the module <idlmodule>com.sun.star.sheet</idlmodule>. Several services of the spreadsheet application representing cells and cell ranges extend the common services from the module com::sun::star::table. The following table shows the services for cells and cell ranges.

{|border="1" cellpadding=4 style="border-collapse:collapse;"
|-bgcolor=#EDEDED
!Spreadsheet service
!Included com::sun::star::table service
|-
|
<idl>com.sun.star.sheet.SheetCell</idl>
|
<idl>com.sun.star.table.Cell</idl>
|-
|
<idl>com.sun.star.sheet.Cells</idl>
| -
|-
|
<idl>com.sun.star.sheet.SheetCellRange</idl>
|
<idl>com.sun.star.table.CellRange</idl>
|-
|
<idl>com.sun.star.sheet.SheetCellRanges</idl>
| -
|-
|
<idl>com.sun.star.sheet.SheetCellCursor</idl>
|
<idl>com.sun.star.table.CellCursor</idl>
|}

The spreadsheet document model in the {{PRODUCTNAME}} API has five major architectural areas (see ''Illustration 9.1'') The five areas are:

* Spreadsheets Container
* Service Manager (document internal)
* DrawPages
* Content Properties
* Objects for Styling

[[Image:SpreadsheetDocumentOverview.png|none|thumb|400px|Spreadsheet Document Component]]

The core of the spreadsheet document model are the spreadsheets contained in the spreadsheets container. When working with document data, almost everything happens in the spreadsheet objects extracted from the spreadsheets container.

The service manager of the spreadsheet document model creates shape objects, text fields for page headers and form controls that can be added to spreadsheets. Note, that the document service manager is different from the main service manager used when connecting to the office. Each document model has its own service manager, so that the services can be adapted to the document they are required for. For instance, a text field is ordered and inserted into the page header text of a sheet using <idlml>com.sun.star.text.XText:insertTextContent</idlml>() or the service manager is asked for a shape object and inserts it into a sheet using <code>add()</code> at the drawpage.

Each sheet in a spreadsheet document can have a drawpage for drawing contents. A drawpage can be visualized as a transparent layer above a sheet. The spreadsheet model is able to provide all drawpages in a spreadsheet document at once.

Linked and named contents from all sheets are accessed through content properties at the document model. There are no content suppliers as in text documents, because the actual content of a spreadsheet document lies in its sheet objects. Rather, there are only certain properties for named and linked contents in all sheets.

Finally, there are services that allow for document wide styling and structuring of the spreadsheet document. Among them are style family suppliers for cells and pages, and a number formats supplier.

Besides these five architectural areas, there are document and calculation aspects shown at the bottom of the illustration. The document aspects of our model are: it is printable, storable, and modifiable, it can be protected and audited, and it supplies general information about itself. On the lower left of the illustration, the calculation aspects are listed. Although almost all spreadsheet functionality can be found at the spreadsheet objects, a few common functions are bound to the spreadsheet document model: goal seeking, consolidation and recalculation of all cells.

Finally, the document model has a controller that provides access to the graphical user interface of the model and has knowledge about the current view status in the user interface (see the upper left of the illustration).

The usage of the spreadsheet objects in the spreadsheets container is discussed in detail in the section [[Documentation/DevGuide/Spreadsheets/Working With Spreadsheet Documents|Working With Spreadsheet Documents]]. Before discussing spreadsheet objects, consider two examples and how they handle a spreadsheet document, that is, how to create, open, save and print.

{{PDL1}}

[[Category:Documentation/Developer's Guide/Spreadsheet Documents]]

Documentation/How Tos/Calc: FIND function

2013-07-18T15:30:42Z

GreatBigBore: Added comment to show case sensitivity, for consistency with SEARCH() documentation.

{{Documentation/MasterTOC
|bookid=1234'''
|booktitle=<div style="padding: 8px; font-size: 140%; font-weight: bold; background-color: #9BC0F5;">CALC FUNCTIONS</div>
|ShowParttitle=block|parttitle=[[Documentation/How_Tos/Calc:_Text_functions|<div style="font-size: 140%;">Text Functions]]
|ShowNextPage=block|NextPage=Documentation/How_Tos/Calc:_FIXED_function
|ShowPrevPage=block|PrevPage=Documentation/How_Tos/Calc:_EXACT_function
|ShowPrevPart=block|PrevPart=Documentation/How_Tos/Calc:_Statistical_functions
|ShowNextPart=block|NextPart=Documentation/Reference/Calc_functions
|toccontent= <div style="padding: 4px; font-size: 130%; font-weight: hidden; background-color:#DCE9FC;">FUNCTIONS</div>

* [[Documentation/How_Tos/Calc:_BASE_function|<div style="font-size: 120%;">Base]]
* [[Documentation/How_Tos/Calc:_CHAR_function|<div style="font-size: 120%;">Char]]
* [[Documentation/How_Tos/Calc:_CLEAN_function|<div style="font-size: 120%;">Clean]]
* [[Documentation/How_Tos/Calc:_CODE_function|<div style="font-size: 120%;">Code]]
* [[Documentation/How_Tos/Calc:_CONCATENATE_function|<div style="font-size: 120%;">Concatenate]]
* [[Documentation/How_Tos/Calc:_DOLLAR_function|<div style="font-size: 120%;">Dollar]]
* [[Documentation/How_Tos/Calc:_EXACT_function|<div style="font-size: 120%;">Exact]]
* [[Documentation/How_Tos/Calc:_FIND_function|<div style="font-size: 120%; border-style: double; border-color:#778899;">Find]]
* [[Documentation/How_Tos/Calc:_FIXED_function|<div style="font-size: 120%;">Fixed]]
* [[Documentation/How_Tos/Calc:_LEFT_function|<div style="font-size: 120%;">Left]]
* [[Documentation/How_Tos/Calc:_LEN_function|<div style="font-size: 120%;">Len]]
* [[Documentation/How_Tos/Calc:_LOWER_function|<div style="font-size: 120%;">Lower]]
* [[Documentation/How_Tos/Calc:_MID_function|<div style="font-size: 120%;">Mid]]
* [[Documentation/How_Tos/Calc:_PROPER_function|<div style="font-size: 120%;">Proper]]
* [[Documentation/How_Tos/Calc:_REPLACE_function|<div style="font-size: 120%;">Replace]]
* [[Documentation/How_Tos/Calc:_REPT_function|<div style="font-size: 120%;">Rept]]
* [[Documentation/How_Tos/Calc:_RIGHT_function|<div style="font-size: 120%;">Right]]
* [[Documentation/How_Tos/Calc:_SEARCH_function|<div style="font-size: 120%;">Search]]
* [[Documentation/How_Tos/Calc:_SUBSTITUTE_function|<div style="font-size: 120%;">Substitute]]
* [[Documentation/How_Tos/Calc:_T_function|<div style="font-size: 120%;">T]]
* [[Documentation/How_Tos/Calc:_TEXT_function|<div style="font-size: 120%;">Text]]
* [[Documentation/How_Tos/Calc:_TRIM_function|<div style="font-size: 120%;">Trim]]
* [[Documentation/How_Tos/Calc:_UPPER_function|<div style="font-size: 120%;">Upper]]
* [[Documentation/How_Tos/Calc:_VALUE_function|<div style="font-size: 120%;">Value]]
}}__TOC__
== FIND ==
Returns the position of a string of text within another string.

=== Syntax: ===
<tt>'''FIND(findtext; texttosearch; startposition)'''</tt>
: returns the character position of the first occurrence of <tt>'''findtext'''</tt> within <tt>'''texttosearch'''</tt>.

: <tt>'''startposition'''</tt> (optional) is the position from which the search starts.

: The search is case-sensitive. For case-insensitive search, see '''SEARCH()'''.

: The search will '''not''' use regular expressions. For searching with regular expressions, see '''SEARCH()'''.

: A failed search gives the <tt>'''#VALUE!'''</tt> error.

: In '''Tools - Options - OpenOffice.org Calc - Calculate''' the setting for <i>Search criteria = and <> must apply to whole cells</i> has no effect.

=== Example: ===
<tt>'''FIND("yo"; "Yoyo")'''</tt>
: returns <tt>'''3'''</tt>. The search is case-sensitive.

<tt>'''FIND("cho"; "choochoo"; 2)'''</tt>
: returns <tt>'''5'''</tt>.

<tt>'''FIND("xyz","abcdef",1)'''</tt>
: returns <tt>'''#VALUE!'''</tt>.
: NOTE: This is an error condition, which must be 'handled' if used as the argument to another function.
: <tt>'''IF( FIND("xyz","abcdef",1) , "Substring Present", "ERR: Missing Substring" )'''</tt>
:: returns <tt>'''#VALUE!'''</tt> which is not very useful, therefore we could use either <tt>'''ISERROR()'''</tt> or <tt>'''ISNUMBER()'''</tt> for example:
:: <tt>'''IF( ISERROR( FIND("xyz","abcdef",1) ) , "ERR: Missing Substring", "Substring Present" )'''</tt>.
::: returns <tt>'''"ERR: Missing Substring"'''</tt> (... allowing the IF() to function, and not propagating the error from the FIND() function.
:: <tt>'''IF( ISNUMBER( FIND("xyz","abcdef",1) ) , "Substring Present", "ERR: Missing Substring" )'''</tt>.
::: returns <tt>'''"ERR: Missing Substring"'''</tt> (... allowing the IF() to function, and not propagating the error from the FIND() function.
:: NOTE: In practice, it may be more maintainable to use ISNUMBER() to avoid negative logic, and it is more indicative of the evaluation desired: if the substring has a position, then ISNUMBER() is TRUE, else ISNUMBER() is FALSE.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: REPLACE function|REPLACE]]
* [[Documentation/How_Tos/Calc: SEARCH function|SEARCH]]
* [[Documentation/How_Tos/Calc: SUBSTITUTE function|SUBSTITUTE]]
* [[Documentation/How_Tos/Calc: EXACT function|EXACT]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: SEARCH function

2013-07-18T15:25:54Z

GreatBigBore: Typo

{{Documentation/MasterTOC
|bookid=1234'''
|booktitle=<div style="padding: 8px; font-size: 140%; font-weight: bold; background-color: #9BC0F5;">CALC FUNCTIONS</div>
|ShowParttitle=block|parttitle=[[Documentation/How_Tos/Calc:_Text_functions|<div style="font-size: 140%;">Text Functions]]
|ShowNextPage=block|NextPage=Documentation/How_Tos/Calc:_SUBSTITUTE_function
|ShowPrevPage=block|PrevPage=Documentation/How_Tos/Calc:_RIGHT_function
|ShowPrevPart=block|PrevPart=Documentation/How_Tos/Calc:_Statistical_functions
|ShowNextPart=block|NextPart=Documentation/Reference/Calc_functions
|toccontent= <div style="padding: 4px; font-size: 130%; font-weight: hidden; background-color:#DCE9FC;">FUNCTIONS</div>

* [[Documentation/How_Tos/Calc:_BASE_function|<div style="font-size: 120%;">Base]]
* [[Documentation/How_Tos/Calc:_CHAR_function|<div style="font-size: 120%;">Char]]
* [[Documentation/How_Tos/Calc:_CLEAN_function|<div style="font-size: 120%;">Clean]]
* [[Documentation/How_Tos/Calc:_CODE_function|<div style="font-size: 120%;">Code]]
* [[Documentation/How_Tos/Calc:_CONCATENATE_function|<div style="font-size: 120%;">Concatenate]]
* [[Documentation/How_Tos/Calc:_DOLLAR_function|<div style="font-size: 120%;">Dollar]]
* [[Documentation/How_Tos/Calc:_EXACT_function|<div style="font-size: 120%;">Exact]]
* [[Documentation/How_Tos/Calc:_FIND_function|<div style="font-size: 120%;">Find]]
* [[Documentation/How_Tos/Calc:_FIXED_function|<div style="font-size: 120%;">Fixed]]
* [[Documentation/How_Tos/Calc:_LEFT_function|<div style="font-size: 120%;">Left]]
* [[Documentation/How_Tos/Calc:_LEN_function|<div style="font-size: 120%;">Len]]
* [[Documentation/How_Tos/Calc:_LOWER_function|<div style="font-size: 120%;">Lower]]
* [[Documentation/How_Tos/Calc:_MID_function|<div style="font-size: 120%;">Mid]]
* [[Documentation/How_Tos/Calc:_PROPER_function|<div style="font-size: 120%;">Proper]]
* [[Documentation/How_Tos/Calc:_REPLACE_function|<div style="font-size: 120%;">Replace]]
* [[Documentation/How_Tos/Calc:_REPT_function|<div style="font-size: 120%;">Rept]]
* [[Documentation/How_Tos/Calc:_RIGHT_function|<div style="font-size: 120%;">Right]]
* [[Documentation/How_Tos/Calc:_SEARCH_function|<div style="font-size: 120%; border-style: double; border-color:#778899;">Search]]
* [[Documentation/How_Tos/Calc:_SUBSTITUTE_function|<div style="font-size: 120%;">Substitute]]
* [[Documentation/How_Tos/Calc:_T_function|<div style="font-size: 120%;">T]]
* [[Documentation/How_Tos/Calc:_TEXT_function|<div style="font-size: 120%;">Text]]
* [[Documentation/How_Tos/Calc:_TRIM_function|<div style="font-size: 120%;">Trim]]
* [[Documentation/How_Tos/Calc:_UPPER_function|<div style="font-size: 120%;">Upper]]
* [[Documentation/How_Tos/Calc:_VALUE_function|<div style="font-size: 120%;">Value]]
}}__TOC__

== SEARCH ==
Returns the position of a string of text within another string.

=== Syntax: ===
<tt>'''SEARCH(findtext; texttosearch; startposition)'''</tt>
: returns the character position of the first occurrence of <tt>'''findtext'''</tt> within <tt>'''texttosearch'''</tt>.

: <tt>'''startposition'''</tt> (optional) is the position from which the search starts.

: The search is '''not''' case-sensitive.

: The search will use regular expressions, if they are enabled ('''Tools - Options - OpenOffice.org Calc - Calculate''').

: A failed search gives the <tt>'''#VALUE!'''</tt> error.

: In '''Tools - Options - OpenOffice.org Calc - Calculate''' the setting for <i>Search criteria = and <>must apply to whole cells</i> has no effect.

=== Example: ===
<tt>'''SEARCH("yo"; "Yoyo")'''</tt>
: returns <tt>'''1'''</tt>. The search is case-insensitive.

<tt>'''SEARCH("cho"; "choochoo"; 2)'''</tt>
: returns <tt>'''5'''</tt>.

<tt>'''SEARCH("t.n"; "often")'''</tt>
: returns <tt>'''3'''</tt>, if regular expressions are enabled. The "." stands for any single character in a regular expression, so <tt>'''"t.n"'''</tt> matches <tt>'''"ten"'''</tt>.

<tt>'''SEARCH("xyz","abcdef",1)'''</tt>
: returns <tt>'''#VALUE!'''</tt>.
: NOTE: This is an error condition, which must be 'handled' if used as the argument to another function.
: <tt>'''IF( SEARCH("xyz","abcdef",1) , "Substring Present", "ERR: Missing Substring" )'''</tt>
:: returns <tt>'''#VALUE!'''</tt> which is not very useful, therefore we could use either <tt>'''ISERROR()'''</tt> or <tt>'''ISNUMBER()'''</tt> for example:
:: <tt>'''IF( ISERROR( SEARCH("xyz","abcdef",1) ) , "ERR: Missing Substring", "Substring Present" )'''</tt>.
::: returns <tt>'''"ERR: Missing Substring"'''</tt> (... allowing the IF() to function, and not propagating the error from the SEARCH() function.
:: <tt>'''IF( ISNUMBER( SEARCH("xyz","abcdef",1) ) , "Substring Present", "ERR: Missing Substring" )'''</tt>.
::: returns <tt>'''"ERR: Missing Substring"'''</tt> (... allowing the IF() to function, and not propagating the error from the SEARCH() function.
:: NOTE: In practice, it may be more maintainable to use ISNUMBER() to avoid negative logic, and it is more indicative of the evaluation desired: if the substring has a position, then ISNUMBER() is TRUE, else ISNUMBER() is FALSE.
{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: REPLACE function|REPLACE]]
* [[Documentation/How_Tos/Calc: FIND function|FIND]]
* [[Documentation/How_Tos/Calc: SUBSTITUTE function|SUBSTITUTE]]
* [[Documentation/How_Tos/Calc: EXACT function|EXACT]]

* [[Documentation/How_Tos/Regular Expressions in Calc|Regular Expressions in Calc]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: FIND function

2013-07-18T15:24:34Z

GreatBigBore: Typo

{{Documentation/MasterTOC
|bookid=1234'''
|booktitle=<div style="padding: 8px; font-size: 140%; font-weight: bold; background-color: #9BC0F5;">CALC FUNCTIONS</div>
|ShowParttitle=block|parttitle=[[Documentation/How_Tos/Calc:_Text_functions|<div style="font-size: 140%;">Text Functions]]
|ShowNextPage=block|NextPage=Documentation/How_Tos/Calc:_FIXED_function
|ShowPrevPage=block|PrevPage=Documentation/How_Tos/Calc:_EXACT_function
|ShowPrevPart=block|PrevPart=Documentation/How_Tos/Calc:_Statistical_functions
|ShowNextPart=block|NextPart=Documentation/Reference/Calc_functions
|toccontent= <div style="padding: 4px; font-size: 130%; font-weight: hidden; background-color:#DCE9FC;">FUNCTIONS</div>

* [[Documentation/How_Tos/Calc:_BASE_function|<div style="font-size: 120%;">Base]]
* [[Documentation/How_Tos/Calc:_CHAR_function|<div style="font-size: 120%;">Char]]
* [[Documentation/How_Tos/Calc:_CLEAN_function|<div style="font-size: 120%;">Clean]]
* [[Documentation/How_Tos/Calc:_CODE_function|<div style="font-size: 120%;">Code]]
* [[Documentation/How_Tos/Calc:_CONCATENATE_function|<div style="font-size: 120%;">Concatenate]]
* [[Documentation/How_Tos/Calc:_DOLLAR_function|<div style="font-size: 120%;">Dollar]]
* [[Documentation/How_Tos/Calc:_EXACT_function|<div style="font-size: 120%;">Exact]]
* [[Documentation/How_Tos/Calc:_FIND_function|<div style="font-size: 120%; border-style: double; border-color:#778899;">Find]]
* [[Documentation/How_Tos/Calc:_FIXED_function|<div style="font-size: 120%;">Fixed]]
* [[Documentation/How_Tos/Calc:_LEFT_function|<div style="font-size: 120%;">Left]]
* [[Documentation/How_Tos/Calc:_LEN_function|<div style="font-size: 120%;">Len]]
* [[Documentation/How_Tos/Calc:_LOWER_function|<div style="font-size: 120%;">Lower]]
* [[Documentation/How_Tos/Calc:_MID_function|<div style="font-size: 120%;">Mid]]
* [[Documentation/How_Tos/Calc:_PROPER_function|<div style="font-size: 120%;">Proper]]
* [[Documentation/How_Tos/Calc:_REPLACE_function|<div style="font-size: 120%;">Replace]]
* [[Documentation/How_Tos/Calc:_REPT_function|<div style="font-size: 120%;">Rept]]
* [[Documentation/How_Tos/Calc:_RIGHT_function|<div style="font-size: 120%;">Right]]
* [[Documentation/How_Tos/Calc:_SEARCH_function|<div style="font-size: 120%;">Search]]
* [[Documentation/How_Tos/Calc:_SUBSTITUTE_function|<div style="font-size: 120%;">Substitute]]
* [[Documentation/How_Tos/Calc:_T_function|<div style="font-size: 120%;">T]]
* [[Documentation/How_Tos/Calc:_TEXT_function|<div style="font-size: 120%;">Text]]
* [[Documentation/How_Tos/Calc:_TRIM_function|<div style="font-size: 120%;">Trim]]
* [[Documentation/How_Tos/Calc:_UPPER_function|<div style="font-size: 120%;">Upper]]
* [[Documentation/How_Tos/Calc:_VALUE_function|<div style="font-size: 120%;">Value]]
}}__TOC__
== FIND ==
Returns the position of a string of text within another string.

=== Syntax: ===
<tt>'''FIND(findtext; texttosearch; startposition)'''</tt>
: returns the character position of the first occurrence of <tt>'''findtext'''</tt> within <tt>'''texttosearch'''</tt>.

: <tt>'''startposition'''</tt> (optional) is the position from which the search starts.

: The search is case-sensitive. For case-insensitive search, see '''SEARCH()'''.

: The search will '''not''' use regular expressions. For searching with regular expressions, see '''SEARCH()'''.

: A failed search gives the <tt>'''#VALUE!'''</tt> error.

: In '''Tools - Options - OpenOffice.org Calc - Calculate''' the setting for <i>Search criteria = and <> must apply to whole cells</i> has no effect.

=== Example: ===
<tt>'''FIND("yo"; "Yoyo")'''</tt>
: returns <tt>'''3'''</tt>.

<tt>'''FIND("cho"; "choochoo"; 2)'''</tt>
: returns <tt>'''5'''</tt>.

<tt>'''FIND("xyz","abcdef",1)'''</tt>
: returns <tt>'''#VALUE!'''</tt>.
: NOTE: This is an error condition, which must be 'handled' if used as the argument to another function.
: <tt>'''IF( FIND("xyz","abcdef",1) , "Substring Present", "ERR: Missing Substring" )'''</tt>
:: returns <tt>'''#VALUE!'''</tt> which is not very useful, therefore we could use either <tt>'''ISERROR()'''</tt> or <tt>'''ISNUMBER()'''</tt> for example:
:: <tt>'''IF( ISERROR( FIND("xyz","abcdef",1) ) , "ERR: Missing Substring", "Substring Present" )'''</tt>.
::: returns <tt>'''"ERR: Missing Substring"'''</tt> (... allowing the IF() to function, and not propagating the error from the FIND() function.
:: <tt>'''IF( ISNUMBER( FIND("xyz","abcdef",1) ) , "Substring Present", "ERR: Missing Substring" )'''</tt>.
::: returns <tt>'''"ERR: Missing Substring"'''</tt> (... allowing the IF() to function, and not propagating the error from the FIND() function.
:: NOTE: In practice, it may be more maintainable to use ISNUMBER() to avoid negative logic, and it is more indicative of the evaluation desired: if the substring has a position, then ISNUMBER() is TRUE, else ISNUMBER() is FALSE.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: REPLACE function|REPLACE]]
* [[Documentation/How_Tos/Calc: SEARCH function|SEARCH]]
* [[Documentation/How_Tos/Calc: SUBSTITUTE function|SUBSTITUTE]]
* [[Documentation/How_Tos/Calc: EXACT function|EXACT]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: FIND function

2013-07-18T15:22:38Z

GreatBigBore: Missed some periods '.'

{{Documentation/MasterTOC
|bookid=1234'''
|booktitle=<div style="padding: 8px; font-size: 140%; font-weight: bold; background-color: #9BC0F5;">CALC FUNCTIONS</div>
|ShowParttitle=block|parttitle=[[Documentation/How_Tos/Calc:_Text_functions|<div style="font-size: 140%;">Text Functions]]
|ShowNextPage=block|NextPage=Documentation/How_Tos/Calc:_FIXED_function
|ShowPrevPage=block|PrevPage=Documentation/How_Tos/Calc:_EXACT_function
|ShowPrevPart=block|PrevPart=Documentation/How_Tos/Calc:_Statistical_functions
|ShowNextPart=block|NextPart=Documentation/Reference/Calc_functions
|toccontent= <div style="padding: 4px; font-size: 130%; font-weight: hidden; background-color:#DCE9FC;">FUNCTIONS</div>

* [[Documentation/How_Tos/Calc:_BASE_function|<div style="font-size: 120%;">Base]]
* [[Documentation/How_Tos/Calc:_CHAR_function|<div style="font-size: 120%;">Char]]
* [[Documentation/How_Tos/Calc:_CLEAN_function|<div style="font-size: 120%;">Clean]]
* [[Documentation/How_Tos/Calc:_CODE_function|<div style="font-size: 120%;">Code]]
* [[Documentation/How_Tos/Calc:_CONCATENATE_function|<div style="font-size: 120%;">Concatenate]]
* [[Documentation/How_Tos/Calc:_DOLLAR_function|<div style="font-size: 120%;">Dollar]]
* [[Documentation/How_Tos/Calc:_EXACT_function|<div style="font-size: 120%;">Exact]]
* [[Documentation/How_Tos/Calc:_FIND_function|<div style="font-size: 120%; border-style: double; border-color:#778899;">Find]]
* [[Documentation/How_Tos/Calc:_FIXED_function|<div style="font-size: 120%;">Fixed]]
* [[Documentation/How_Tos/Calc:_LEFT_function|<div style="font-size: 120%;">Left]]
* [[Documentation/How_Tos/Calc:_LEN_function|<div style="font-size: 120%;">Len]]
* [[Documentation/How_Tos/Calc:_LOWER_function|<div style="font-size: 120%;">Lower]]
* [[Documentation/How_Tos/Calc:_MID_function|<div style="font-size: 120%;">Mid]]
* [[Documentation/How_Tos/Calc:_PROPER_function|<div style="font-size: 120%;">Proper]]
* [[Documentation/How_Tos/Calc:_REPLACE_function|<div style="font-size: 120%;">Replace]]
* [[Documentation/How_Tos/Calc:_REPT_function|<div style="font-size: 120%;">Rept]]
* [[Documentation/How_Tos/Calc:_RIGHT_function|<div style="font-size: 120%;">Right]]
* [[Documentation/How_Tos/Calc:_SEARCH_function|<div style="font-size: 120%;">Search]]
* [[Documentation/How_Tos/Calc:_SUBSTITUTE_function|<div style="font-size: 120%;">Substitute]]
* [[Documentation/How_Tos/Calc:_T_function|<div style="font-size: 120%;">T]]
* [[Documentation/How_Tos/Calc:_TEXT_function|<div style="font-size: 120%;">Text]]
* [[Documentation/How_Tos/Calc:_TRIM_function|<div style="font-size: 120%;">Trim]]
* [[Documentation/How_Tos/Calc:_UPPER_function|<div style="font-size: 120%;">Upper]]
* [[Documentation/How_Tos/Calc:_VALUE_function|<div style="font-size: 120%;">Value]]
}}__TOC__
== FIND ==
Returns the position of a string of text within another string.

=== Syntax: ===
<tt>'''FIND(findtext; texttosearch; startposition)'''</tt>
: returns the character position of the first occurrence of <tt>'''findtext'''</tt> within <tt>'''texttosearch'''</tt>.

: <tt>'''startposition'''</tt> (optional) is the position from which the search starts.

: The search is case-sensitive. For case-insensitive search, see '''SEARCH()'''.

: The search will '''not''' use regular expressions. For searching with regular expressions, see '''SEARCH()'''.

: A failed search gives the <tt>'''#VALUE!'''</tt> error.

: In '''Tools - Options - OpenOffice.org Calc - Calculate''' the setting for <i>Search criteria = and <> must apply to whole cells</i> has no effect.

=== Example: ===
<tt>'''FIND("yo"; "Yoyo")'''</tt>
: returns <tt>'''3'''</tt>.

<tt>'''FIND("cho"; "choochoo"; 2)'''</tt>
: returns <tt>'''5'''</tt>.

<tt>'''FIND("xyz","abcdef",1)'''</tt>
: returns <tt>'''#VALUE!'''</tt>.
: NOTE: This is an error condition, which must be 'handled' if used as the argument to another function.
: <tt>'''IF( FIND("xyz","abcdef",1) , "Substring Present", "ERR: Missing Substring" )'''</tt>
:: returns <tt>'''#VALUE!'''</tt> which is not very useful, therefore we could use either <tt>'''ISERROR()'''</tt> or <tt>'''ISNUMBER()'''</tt> for example:
:: <tt>'''IF( ISERROR( FIND("xyz","abcdef",1) ) , "ERR: Missing Substring", "Substring Present" )'''</tt>.
::: returns <tt>'''"ERR: Missing Substring"'''</tt> (... allowing the IF() to function, and not propagating the error from the FIND() function.
:: <tt>'''IF( ISNUMBER( FIND("xyz","abcdef",1) ) , "Substring Present", "ERR: Missing Substring" )'''</tt>.
::: returns <tt>'''"ERR: Missing Substring"'''</tt> (... allowing the IF() to function, and not propagating the error from the FIND() function.
:: NOTE: In practice, it may be more maintainable to use ISNUMBER() to avoid negative logic, and it is more indicitive of the evaluation desired: if the substring has a position, then ISNUMBER() is TRUE, else ISNUMBER() is FALSE.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: REPLACE function|REPLACE]]
* [[Documentation/How_Tos/Calc: SEARCH function|SEARCH]]
* [[Documentation/How_Tos/Calc: SUBSTITUTE function|SUBSTITUTE]]
* [[Documentation/How_Tos/Calc: EXACT function|EXACT]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: FIND function

2013-07-18T15:21:25Z

GreatBigBore: Indicated that the function does not use regex; referred to SEARCH() for case-insensitive and regex searching; added note for =/<> setting, to be consistent with SEARCH() documentation

{{Documentation/MasterTOC
|bookid=1234'''
|booktitle=<div style="padding: 8px; font-size: 140%; font-weight: bold; background-color: #9BC0F5;">CALC FUNCTIONS</div>
|ShowParttitle=block|parttitle=[[Documentation/How_Tos/Calc:_Text_functions|<div style="font-size: 140%;">Text Functions]]
|ShowNextPage=block|NextPage=Documentation/How_Tos/Calc:_FIXED_function
|ShowPrevPage=block|PrevPage=Documentation/How_Tos/Calc:_EXACT_function
|ShowPrevPart=block|PrevPart=Documentation/How_Tos/Calc:_Statistical_functions
|ShowNextPart=block|NextPart=Documentation/Reference/Calc_functions
|toccontent= <div style="padding: 4px; font-size: 130%; font-weight: hidden; background-color:#DCE9FC;">FUNCTIONS</div>

* [[Documentation/How_Tos/Calc:_BASE_function|<div style="font-size: 120%;">Base]]
* [[Documentation/How_Tos/Calc:_CHAR_function|<div style="font-size: 120%;">Char]]
* [[Documentation/How_Tos/Calc:_CLEAN_function|<div style="font-size: 120%;">Clean]]
* [[Documentation/How_Tos/Calc:_CODE_function|<div style="font-size: 120%;">Code]]
* [[Documentation/How_Tos/Calc:_CONCATENATE_function|<div style="font-size: 120%;">Concatenate]]
* [[Documentation/How_Tos/Calc:_DOLLAR_function|<div style="font-size: 120%;">Dollar]]
* [[Documentation/How_Tos/Calc:_EXACT_function|<div style="font-size: 120%;">Exact]]
* [[Documentation/How_Tos/Calc:_FIND_function|<div style="font-size: 120%; border-style: double; border-color:#778899;">Find]]
* [[Documentation/How_Tos/Calc:_FIXED_function|<div style="font-size: 120%;">Fixed]]
* [[Documentation/How_Tos/Calc:_LEFT_function|<div style="font-size: 120%;">Left]]
* [[Documentation/How_Tos/Calc:_LEN_function|<div style="font-size: 120%;">Len]]
* [[Documentation/How_Tos/Calc:_LOWER_function|<div style="font-size: 120%;">Lower]]
* [[Documentation/How_Tos/Calc:_MID_function|<div style="font-size: 120%;">Mid]]
* [[Documentation/How_Tos/Calc:_PROPER_function|<div style="font-size: 120%;">Proper]]
* [[Documentation/How_Tos/Calc:_REPLACE_function|<div style="font-size: 120%;">Replace]]
* [[Documentation/How_Tos/Calc:_REPT_function|<div style="font-size: 120%;">Rept]]
* [[Documentation/How_Tos/Calc:_RIGHT_function|<div style="font-size: 120%;">Right]]
* [[Documentation/How_Tos/Calc:_SEARCH_function|<div style="font-size: 120%;">Search]]
* [[Documentation/How_Tos/Calc:_SUBSTITUTE_function|<div style="font-size: 120%;">Substitute]]
* [[Documentation/How_Tos/Calc:_T_function|<div style="font-size: 120%;">T]]
* [[Documentation/How_Tos/Calc:_TEXT_function|<div style="font-size: 120%;">Text]]
* [[Documentation/How_Tos/Calc:_TRIM_function|<div style="font-size: 120%;">Trim]]
* [[Documentation/How_Tos/Calc:_UPPER_function|<div style="font-size: 120%;">Upper]]
* [[Documentation/How_Tos/Calc:_VALUE_function|<div style="font-size: 120%;">Value]]
}}__TOC__
== FIND ==
Returns the position of a string of text within another string.

=== Syntax: ===
<tt>'''FIND(findtext; texttosearch; startposition)'''</tt>
: returns the character position of the first occurrence of <tt>'''findtext'''</tt> within <tt>'''texttosearch'''</tt>.

: <tt>'''startposition'''</tt> (optional) is the position from which the search starts.

: The search is case-sensitive. For case-insensitive search, see '''SEARCH()'''

: The search will '''not''' use regular expressions. For searching with regular expressions, see '''SEARCH()'''

: A failed search gives the <tt>'''#VALUE!'''</tt> error.

: In '''Tools - Options - OpenOffice.org Calc - Calculate''' the setting for <i>Search criteria = and <> must apply to whole cells</i> has no effect.

=== Example: ===
<tt>'''FIND("yo"; "Yoyo")'''</tt>
: returns <tt>'''3'''</tt>.

<tt>'''FIND("cho"; "choochoo"; 2)'''</tt>
: returns <tt>'''5'''</tt>.

<tt>'''FIND("xyz","abcdef",1)'''</tt>
: returns <tt>'''#VALUE!'''</tt>.
: NOTE: This is an error condition, which must be 'handled' if used as the argument to another function.
: <tt>'''IF( FIND("xyz","abcdef",1) , "Substring Present", "ERR: Missing Substring" )'''</tt>
:: returns <tt>'''#VALUE!'''</tt> which is not very useful, therefore we could use either <tt>'''ISERROR()'''</tt> or <tt>'''ISNUMBER()'''</tt> for example:
:: <tt>'''IF( ISERROR( FIND("xyz","abcdef",1) ) , "ERR: Missing Substring", "Substring Present" )'''</tt>.
::: returns <tt>'''"ERR: Missing Substring"'''</tt> (... allowing the IF() to function, and not propagating the error from the FIND() function.
:: <tt>'''IF( ISNUMBER( FIND("xyz","abcdef",1) ) , "Substring Present", "ERR: Missing Substring" )'''</tt>.
::: returns <tt>'''"ERR: Missing Substring"'''</tt> (... allowing the IF() to function, and not propagating the error from the FIND() function.
:: NOTE: In practice, it may be more maintainable to use ISNUMBER() to avoid negative logic, and it is more indicitive of the evaluation desired: if the substring has a position, then ISNUMBER() is TRUE, else ISNUMBER() is FALSE.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: REPLACE function|REPLACE]]
* [[Documentation/How_Tos/Calc: SEARCH function|SEARCH]]
* [[Documentation/How_Tos/Calc: SUBSTITUTE function|SUBSTITUTE]]
* [[Documentation/How_Tos/Calc: EXACT function|EXACT]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/BASIC Guide/Strings (Runtime Library)

2013-07-15T21:17:19Z

GreatBigBore: Changed return value name because it suggested that the return value from InStr is a string. It is a number, not a string. Changed parameter name for clarity.

{{Documentation/BASICGuideTOC/v2
|ShowPrevNext=block
|ShowPrevPage=block
|PrevPage=Documentation/BASIC Guide/Conversion Functions (Runtime Library)
|NextPage=Documentation/BASIC Guide/Date and Time (Runtime Library)
|runtime=block
}}
{{DISPLAYTITLE:Strings ({{OOo}} BASIC Runtime Library)}}
__NOTOC__
== Working with Sets of Characters ==

When administering strings, {{OOo}} Basic uses the set of Unicode characters. The <tt>Asc</tt> and <tt>Chr</tt> functions allow the Unicode value belonging to a character to be established and/or the corresponding character to be found for a Unicode value. The following expressions assign the various Unicode values to the code variable:

<source lang="oobas">
Code = Asc("A") ' Latin letter A (Unicode-value 65)
Code = Asc("€") ' Euro character (Unicode-value 8364)
Code = Asc("Л") ' Cyrillic letter Л (Unicode-value 1083)
</source>

Conversely, the expression

<source lang="oobas">
MyString = Chr(13)
</source>

ensures that the <tt>MyString</tt> string is initialized with the value of the number <tt>13</tt>, which stands for a hard line break.

The <tt>Chr</tt> command is often used in Basic languages to insert control characters in a string. The assignment

<source lang="oobas">
MyString = Chr(9) + "This is a test" + Chr(13)
</source>

therefore ensures that the text is preceded by a tab character (Unicode-value 9) and that a hard line break (Unicode-value 13) is added after the text.

== Accessing Parts of a String ==

{{OOo}} Basic provides three functions that return partial strings, plus a length function:

;<tt>Left(MyString, Length)</tt>:returns the first <tt>Length</tt> characters of <tt>MyString</tt>.
;<tt>Right(MyString, Length)</tt>:returns the last <tt>Length</tt> characters of <tt>MyString</tt>.
;<tt>Mid(MyString, Start, Length)</tt>:returns first <tt>Length</tt> characters of <tt>MyString</tt> as of the <tt>Start</tt> position.
;<tt>Len(MyString)</tt>:returns the number of characters in <tt>MyString</tt>.

Unlike array subscripts, character positions in a string start with <tt>1</tt>. Here are a few example calls for the named functions:

<source lang="oobas">
Dim MyString As String
Dim MyResult As String
Dim MyLen As Integer

MyString = "This is a small test"
MyResult = Left(MyString,5) ' Provides the string "This "
MyResult = Right(MyString, 5) ' Provides the string " test"
MyResult = Mid(MyString, 8, 5) ' Provides the string " a sm"
MyLen = Len(MyString) ' Provides the value 20
</source>

== Search and Replace ==

{{OOo}} Basic provides the <tt>InStr</tt> function for searching for a partial string within another string:

<source lang="oobas">
PositionOfMatch = InStr(MyString, StringToFind)
</source>

The <tt>StringToFind</tt> parameter specifies the string to be searched for within <tt>MyString</tt>. The function returns a number that contains the position at which the <tt>StringToFind</tt> first appears within <tt>MyString</tt>; a return value of zero indicates no match. If you want to find other matches for the string, the function also provides the opportunity to specify an optional start position from which {{OOo}} Basic begins the search. In this case, the syntax of the function is:

<source lang="oobas">
PositionOfMatch = InStr(StartPosition, MyString, StringToFind)
</source>

In the previous examples, <tt>InStr</tt> ignores uppercase and lowercase characters. To change the search so that <tt>InStr</tt> is case sensitive, add the parameter <tt>0</tt>, as shown in the following example:

<source lang="oobas">
PositionOfMatch = InStr(MyString, StringToFind, 0)
</source>

Using the previous functions for editing strings, programmers can search for and replace one string in another string:

<source lang="oobas">
Function Replace(Source As String, Search As String, NewPart As String)
Dim Result As String
Dim StartPos As Long
Dim CurrentPos As Long

Result = ""
StartPos = 1
CurrentPos = 1

If Search = "" Then
Result = Source
Else
Do While CurrentPos <> 0
CurrentPos = InStr(StartPos, Source, Search)
If CurrentPos <> 0 Then
Result = Result + Mid(Source, StartPos, _
CurrentPos - StartPos)
Result = Result + NewPart
StartPos = CurrentPos + Len(Search)
Else
Result = Result + Mid(Source, StartPos, Len(Source))
End If ' Position <> 0
Loop
End If

Replace = Result
End Function
</source>

The function searches through the transferred <tt>Search</tt> string in a loop by means of <tt>InStr</tt> in the original term <tt>Source</tt>. If it finds the search term, it takes the part before the expression and writes it to the <tt>Result</tt> return buffer. It adds the <tt>NewPart</tt> section at the point of the search term <tt>Search</tt>. If no more matches are found for the search term, the function establishes the part of the string still remaining and adds this to the return buffer. It returns the string produced in this way as the result of the replacement process.

Since replacing parts of character sequences is one of the most frequently used functions, the <tt>Mid</tt> function in {{OOo}} Basic has been extended so that this task is performed automatically. The following example replaces three characters with the string <tt>is</tt> from the sixth position of the <tt>MyString</tt> string.

<source lang="oobas">
Dim MyString As String

MyString = "This was my text"
Mid(MyString, 6, 3, "is")
</source>

Or this much simpler function can be used. First it splits "Source" dividing it at all "Search", and joins the parts back with "NewPart". So Replace("This is a test", " ", "_") yields "This_is_a_test" :D

<source lang="oobas">
Function Replace(Source As String, Search As String, NewPart As String)
Dim Result As String
Result = join(split(Source, Search), NewPart)
Replace = Result
End Function
</source>

{{Documentation/Caution|When it is used with 4 arguments, to replace a sub-string in a string, <tt>Mid</tt> is an '''instruction''', not a function : it does not return any value !}}

== Formatting Strings ==

The <tt>Format</tt> function formats numbers as a string. To do this, the function expects a <tt>Format</tt> expression to be specified, which is then used as the template for formatting the numbers. Each place holder within the template ensures that this item is formatted correspondingly in the output value. The five most important place holders within a template are the zero (<tt>0</tt>), pound sign (<tt>#</tt>), period (<tt>.</tt>), comma (<tt>,</tt>) and dollar sign (<tt>$</tt>) characters.

The <tt>0</tt> character within the template ensures that a number is always placed at the corresponding point. If a number is not provided, 0 is displayed in its place.

A <tt>.</tt> stands for the decimal point symbol defined by the operating system in the country-specific settings.

The example below shows how the <tt>0</tt> and <tt>.</tt> characters can define the digits after the decimal point in an expression:

<source lang="oobas">
MyFormat = "0.00"
MyString = Format(-1579.8, MyFormat) ' Provides "-1579,80"
MyString = Format(1579.8, MyFormat) ' Provides "1579,80"
MyString = Format(0.4, MyFormat) ' Provides "0,40"
MyString = Format(0.434, MyFormat) ' Provides "0,43"
</source>

In the same way, zeros can be added in front of a number to achieve the desired length:

<source lang="oobas">
MyFormat = "0000.00"
MyString = Format(-1579.8, MyFormat) ' Provides "-1579,80"
MyString = Format(1579.8, MyFormat) ' Provides "1579,80"
MyString = Format(0.4, MyFormat) ' Provides "0000,40"
MyString = Format(0.434, MyFormat) ' Provides "0000,43"
</source>

A <tt>,</tt> represents the character that the operating system uses for a thousands separator, and the <tt>#</tt> stands for a digit or place that is only displayed if it is required by the input string.

<source lang="oobas">
MyFormat = "#,##0.00"
MyString = Format(-1579.8, MyFormat) ' Provides "-1.579,80"
MyString = Format(1579.8, MyFormat) ' Provides "1.579,80"
MyString = Format(0.4, MyFormat) ' Provides "0,40"
MyString = Format(0.434, MyFormat) ' Provides "0,43"
</source>

In place of the <tt>$</tt> place holder, the <tt>Format</tt> function displays the relevant currency symbol defined by the system (this example assumes a European locale has been defined):

<source lang="oobas">
MyFormat = "#,##0.00 $"
MyString = Format(-1579.8, MyFormat) ' Provides "-1.579,80 €"
MyString = Format(1579.8, MyFormat) ' Provides "1.579,80 €"
MyString = Format(0.4, MyFormat) ' Provides "0,40 €"
MyString = Format(0.434, MyFormat) ' Provides "0,43 €"
</source>

The format instructions used in VBA for formatting date and time details can also be used:

<source lang="oobas">
sub main
dim myDate as date
myDate = "01/06/98"
TestStr = Format(myDate, "mm-dd-yyyy") ' 01-06-1998
MsgBox TestStr
end sub
</source>

{{InterWiki Languages BasicGuide|articletitle=Documentation/BASIC Guide/Strings (Runtime Library)}}
{{PDL1}}