inspector text
Find It. Control It. You're In Charge.

What is inspector text?

inspector text is an Add-In for Microsoft Excel* that provides powerful worksheet formula functions for working with text in worksheet cells, including search & replace, and fuzzy matching.

inspector text also provides worksheet formula functions that evaluate concatenated formulas, that return ordinal indicators for cardinal numbers, and that perform Regular Expressions search and replace.

inspector text also provides online help from Excel's Tools menu, and utilizes Excel's Insert Function dialog to make using inspector text functions more convenient.

Search & Replace

inspector text's search and replace worksheet formula functions include itSEARCH, itREPLACE, itCOUNTINCELL, itGETWORD, itEXCLUDE, itEXCLUDENOTNUM, itEXTRACT, itEXTRACTNUM, itREVERSE, itFINDR, itSEARCHR, itREPLACER, itSUBSTITUTER, itMIDR, and itREGEX.

itSEARCH allows extremely versatile and flexible text searching:

• Can use several powerful wildcards and substitutions.
• Can search from left to right or from right to left.
• Can be case sensitive or not.
• Can restrict the search to text that appears before (or does not appear before) other text you specify.
• Can offset both the position and the length of the text found by any amount.
• Can return any of 22 kinds of information about the text found, such as position, position from right, text string found, count of all text strings found, array of all text strings found, etc.
• Can return a friendly, custom message if no match is found, instead of returning a standard error.

itREPLACE allows extremely versatile and flexible text replacement:

• Can use several powerful wildcards and substitutions.
• Can search from left to right or from right to left.
• Can be case sensitive or not.
• Can restrict the search to text that appears before (or does not appear before) other text you specify.
• Can replace only the first matching string found, or all matching strings found.
• Can rearrange text, instead of (or in addition to) replacing it.
• Can return a friendly, custom message if no match is found, instead of returning a standard error.

itCOUNTINCELL counts the occurrences of a given string in a worksheet cell, or within in any text string:

• Allows versatile and flexible criteria.
• Can be case sensitive or not.

itGETWORD finds the nth word in a string:

• Allows replacing word separators (spaces and tabs by default) with a versatile and flexible search string.
• Can search from left to right or from right to left.
• Can be case sensitive or not.
• Can return a friendly, custom message if nth word is not found, instead of returning a standard error.

itEXCLUDE excludes undesired characters and strings from text.

itEXCLUDENOTNUM excludes non-numeric and other undesired characters and/or digits from text:

• Preserves only the first decimal symbol.
• Recognizes the local decimal symbol (a period in the USA, a comma in Germany and France, etc).
• Recognizes negative numbers in either hyphenated format or in parentheses:
  • "-1" is recognized as "negative one".
  • "(1)" is also recognized as "negative one".
• Detects numbers in exponential notation and preserves them.

itEXTRACT retrieves desired characters from text.

itEXTRACTNUM retrieves numeric and other desired characters from text.

• Preserves only the first decimal symbol.
• Recognizes the local decimal symbol (a period in the USA, a comma in Germany and France, etc).
• Recognizes negative numbers in either hyphenated format or in parentheses:
  • "-1" is recognized as "negative one".
  • "(1)" is also recognized as "negative one".
• Detects numbers in exponential notation and preserves them.

itREVERSE reverses the characters in text right to left.

itFINDR is the same as Excel's built-in FIND function, but searches from right to left.

itSEARCHR is the same as Excel's built-in SEARCH function, but searches from right to left.

itREPLACER is the same as Excel's built-in REPLACE function, but counts from right to left.

itSUBSTITUTER is the same as Excel's built-in SUBSTITUTE function, but searches from right to left.

itMIDR is the same as Excel's built-in MID function, but searches from right to left.

itREGEX performs a Regular Expressions search:

• Can replace text.
• Can return any of the following:
  • The value of the first match
  • The position of the first match
  • The length of the first match
  • An array of all matched values
  • An array of all matched positions
  • An array of all matched lengths
  • A count of all matches
• Can be case sensitive or not.
• Can return a friendly, custom message if nth word is not found, instead of returning a standard error.

Fuzzy Comparison

inspector text's fuzzy comparison worksheet formula functions are itISFUZZYMATCH and itFUZZYCOMPARE.

itISFUZZYMATCH compares two text strings and determines whether they are alike enough to meet a minimum fuzzy match score.

itFUZZYCOMPARE compares two text strings, returning the string they have in common, or a fuzzy comparison score.

Miscellaneous

inspector text's miscellaneous worksheet formula functions are itST and itCONCATULA.

itST returns the English ordinal indicator, also known as the ordinal suffix, of a cardinal number. For example, it can turn 1 into 1st, 2 into 2nd, 3 into 3rd, etc:

• Can return the complete ordinal number ("1st", "2nd", "3rd", etc.), or the ordinal indicator alone ("st", "nd", "rd", etc).
• Can return the ordinal indicator in lower case or upper case.

itCONCATULA evaluates a concatenated formula, or any other all-text formula in which the operator is text:

• Can evaluate any of the following arithmetic and comparison operators: +, -, *, ^, /, =, <>, <=, >=.
• ><, =<, and => are accepted and treated as <>, <=, and >= respectively.
• Can be nested to evaluate complex formulas.

Information

inspector text's information worksheet formula functions are itTEST and itVERSION.

itTEST is a quick and easy way to determine whether inspector text is working properly.

itVERSION returns the version number of inspector text.

Online Help

inspector text provides online help from Excel's Tools menu:

Insert Function Dialog

inspector text utilizes Excel's Insert Function dialog to make using inspector text functions more convenient than typing them in directly:

Try the Free Edition

Ready to try the Free Edition of inspector text? You can use it for as long as you like. Download it here. Then, see the Tutorial.

* Although inspector text currently works only with Microsoft Excel for Windows, versions for Lotus 123 and Corel Quattro Pro are possible too, so let me know if you’re interested.

PrecisionCalc Home Page