Table 13 Excel spreadsheet

United States Presidents named John

John

Adams

1797-1801

 

 

 

John Quincy

Adams

1825-1829

 

 

 

John Fitzgerald

Kennedy

1961-1963

 

 

 

John

Tyler

1841-1845

 

 

 

The specific order in which the text in the cells is stored internally depends on:

The version of the product, for example Excel or Quattro Pro, used to generate the spreadsheet

The insertion order for the spreadsheet text

For the spreadsheet above, assuming the cell text for names were entered in displayed order from top left to bottom right (John Adams was entered first) and the title and dates were entered after all the names were entered, most versions of spreadsheets store the text internally as follows:

John

Adams John Quincy John Fitzgerald Kennedy Tyler

United States Presidents named John 1797–1801 1825–1829 1961–1963 1841–1845

Note the following features of the internal representation:

Text sharing: Where certain text appears in more than one cell in the spreadsheet, the text may appear only once in the internal representation. In this example, this is the case with the text “John” and “Adams”. (Note that not all versions of Excel consistently share text in exactly this way.)

This text sharing only occurs at the level of the entire text of a cell, and never occurs within cells. Thus, “John Quincy” and “John Fitzgerald” remain whole and independent.

Even accounting for text sharing, the specific ordering of various cell text in the internal representation does not necessarily follow presentation order, and instead often follows insertion order.

Because of these factors, text sequence matches in an Excel spreadsheet (for example) are only consistent with the spreadsheet as viewed in Excel if the matched text appears wholly within a cell. However, it is possible for sequences to match in inconsistent ways across cell text depending on the precise version and editing history of that spreadsheet.

For the spreadsheet and order of insertion shown above, the following queries would match:

"John Adams"

"Adams John"

"Quincy John"

"John Fitzgerald Kennedy" "Presidents named John"

And, the following queries would not match:

"John Tyler" "Quincy Adams" "John Quincy Adams" "John Adams 1797–1801"

42 Query expression syntax and matching

Page 42
Image 42
HP Integrated Archive Platform manual Excel spreadsheet