[ Home | Contents | Submitted spreadsheets | How to copy your component into Excel
Download Excelsior | Demonstrations and background reading | Excel science-fiction generator
Why modularise? | "Before" and "after" cartoons | Press | Sponsors | Contact | Jocelyn Ireson-Paine

Ireson-Ireson-Paine Spreadsheet Parts Repository
Filter: removes all strings not matching a pattern
Source code

This spreadsheet component removes all text strings that do not match a given pattern.

Inputs

These are the cells to search for occurrences of the pattern pattern, and pattern itself:

constant pattern.

table elements_to_search : elements_base -> text.

Outputs

Element matching_elements[i] is the i'th element of elements_to_search that matches pattern, or blank if there are no more such elements.

table matching_elements : elements_base -> text.

Workings

Element the_index[i] is the index of the i'th element of elements_to_search that matches pattern, or -1 if there are no more such elements.

type elements_base.

table the_index : elements_base -> text.
the_index[ 1 ] =
  if( isna( match( pattern, elements_to_search[all], 0 ) )
    , -1
    , match( pattern, elements_to_search[all], 0 )
    ).

the_index[ i > 1 ] =
  if( the_index[i-1] = -1
      // There was no previous one, so can't be another.
    , -1
    , if( the_index[i-1] = upb(elements_base)
          // Previous one was at the end of the table,
          // so there can't be any more.
        , -1
        , if( isna( match( pattern
                         , elements_to_search[ (the_index[i-1]+1):upb(elements_base) ]
                         , 0
                         )
                  )
            , -1
            , match( pattern
                   , elements_to_search[ (the_index[i-1]+1):upb(elements_base) ]
                   , 0
                   ) + the_index[ i-1 ]
            )
        )
    ).

matching_elements[ i ] =
  if( the_index[ i ] <> -1
    , elements_to_search[ the_index[i] ]
    , ""
    ).