Study guide

5.3 Find Replace Tool: Lookup & Text Substitution

Alteryx Designer Core Certification Exam

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Exam Overview, Designer Interface & Optimization

1.1 Exam Overview, Format & Strategy 1.2 Designer Interface, Canvas & Navigation 1.3 Alteryx Native File Formats & Workflow Packaging 1.4 Workflow Optimization, Caching & Performance

2 Chapter 2: In/Out Tools & Data Inspection

2.1 Input Data Tool Configuration & Formats 2.2 Text Input & Directory Tools 2.3 Output Data Tool Configuration & Options 2.4 Browse Tool & Data Profiling

3 Chapter 3: Core Preparation Tools & Data Types

3.1 Alteryx Data Types & Conversion Rules 3.2 Select Tool Configuration & Unknown Fields 3.3 Filter Tool & True/False Anchors 3.4 Data Cleansing Tool

4 Chapter 4: Advanced Preparation & Record Sampling

4.1 Formula Tool: Syntax, Operators & Conditional Logic 4.2 Formula Tool: Date/Time Functions & Type Casting 4.3 Sort Tool & Dictionary Sorting 4.4 Sample, Select Records & Unique Tools

5 Chapter 5: Blending Data: Join, Union & Append

5.1 Join Tool & Anchor Mechanics 5.2 Union Tool & Output Configurations 5.3 Find Replace Tool: Lookup & Text Substitution 5.4 Append Fields Tool & Cartesian Joins

6 Chapter 6: Parsing Data: Text, Dates & Regular Expressions

6.1 Text to Columns Tool 6.2 DateTime Tool 6.3 RegEx Tool: Methods & Syntax

7 Chapter 7: Transforming Data & Workflow Documentation

7.1 Summarize Tool & Aggregation Functions 7.2 Transpose Tool: Pivoting Wide to Long 7.3 Cross Tab Tool: Pivoting Long to Wide 7.4 Documentation Tools & Workflow Best Practices

8 Chapter 8: Practical Exam Scenarios & Troubleshooting

8.1 Troubleshooting Common Workflow Errors & Data Truncation 8.2 Practical Exam Question Walkthroughs & Modeling 8.3 Comprehensive Exam Strategy & Core Review

5.3 Find Replace Tool: Lookup & Text Substitution

Key Takeaways Explain This Topic AI

The Find Replace tool requires two inputs: F (Find/Source stream) and R (Replace/Lookup reference table), producing 1 combined output stream.

Both search fields (Find within Field in F and Find Value in R) must be string data types; non-string fields cannot be selected in the search configuration.

Find Options support substring matching ('Beginning of Field', 'Any Part of Field', 'Entire Field'), Case Insensitive searches, and Match Whole Word Only.

Replace Options allow either in-place text substitution ('Replace Found Text') or metadata enrichment ('Append Field(s) to Record').

Unlike the Join tool, Find Replace preserves 100% of records from the F stream without dropping unmatched rows, populating unmatched appends with Null.

Last updated: August 2026

5.3 Find Replace Tool: Lookup & Text Substitution

Core Certification Focus: The Find Replace tool performs string searching, text substitution, and dictionary lookups without requiring exact relational join keys. Exam candidates must understand the two input anchors ( F and R ), the mandatory String data type requirement for search keys, the three search location options ( Beginning of Field , Any Part of Field , Entire Field ), the difference between Replace Found Text and Append Field(s) to Record , and why unmatched records in F are never dropped.

1. Tool Architecture & Anchor Mechanics

The Find Replace tool (located in the purple Join category) searches a specified text field in a primary dataset and matches it against a reference lookup table to either substitute text or append reference columns.

Tool Anchor Layout

Input Anchors (2):

F (Find / Source Input): The primary incoming data stream containing records to be searched, updated, or enriched.

R (Replace / Reference Input): The lookup reference table containing the search patterns and replacement/append values.

Output Anchors (1):

Output (Single Output): Outputs the complete dataset from F with substitutions applied or new columns appended. +-----------------------------------------------------------------------------+

  • | FIND REPLACE ANCHOR ARCHITECTURE |
  • | |
  • | [ Primary Data Stream ] ---> (F) |
  • | [ FIND REPLACE ] ---> (1 Output Anchor)|
  • | [ Lookup Reference Table] ---> (R) (Purple) |
  • +-----------------------------------------------------------------------------+
  • 2. Configuration Window Properties

The Find Replace configuration pane is organized into three logical sections: Find Field Mapping, Find Options, and Replace/Append Actions: +-----------------------------------------------------------------------------+

  • | FIND REPLACE CONFIGURATION PANE |
  • +-----------------------------------------------------------------------------+

| FIND:

  • | Find within Field (F): [ CustomerAddress v ]
  • | Find Value (R): [ Abbreviation v ]
  • | --------------------------------------------------------------------------- |

| FIND OPTIONS:

  • | ( ) Beginning of Field (o) Any Part of Field ( ) Entire Field |
  • | [X] Case Insensitive Find [X] Match Whole Word |
  • | --------------------------------------------------------------------------- |

| REPLACE / APPEND OPTIONS:

  • | ( ) Replace Found Text (o) Append Field(s)
  • | Replace With Field: [ FullName v ] [X] StateName |
  • | (o) Replace Multiple Found Items [X] RegionCode |
  • | ( ) Replace First Found Item [ ] TaxRate |
  • +-----------------------------------------------------------------------------+
  • Section 1: Find Field Mapping

Find within Field ( F ): Selects the string column in the primary stream ( F ) that contains text to search.

Find Value ( R ): Selects the string column in the reference stream ( R ) containing search terms.

Data Type Rule: Both fields MUST be String data types ( String , V_String , WString , V_WString ). Numeric or Date fields will not appear in these dropdowns.

Section 2: Find Options

Beginning of Field: Matches only if the search string occurs starting at the very first character (index 0) of the F text field.

Any Part of Field: Substring search; matches if the search string appears anywhere within the F text field.

Entire Field: Exact full-field match; matches only if the entire F cell value equals the R search value from start to end.

Case Insensitive Find (Checkbox): When checked, treats upper and lowercase letters as identical (e.g., "usa" matches "USA" and "Usa" ).

Match Whole Word Only (Checkbox): Prevents partial word substitutions by enforcing word boundary checks (e.g., searching for "cat" will match "the cat sat" but will not match inside "catalog" or "scatter" ).

3. Action Modes: Replace Found Text vs. Append Field(s)

The developer must choose one of two distinct operational modes:

Mode A: Replace Found Text (Substitution Mode)

Overwrites the matched text in the F stream using values from a designated replacement field in R .

Radio Options:

Replace Multiple Found Items: Replaces every instance of the matched search string within each cell.

Replace First Found Item: Replaces only the first occurrence of the matched string in each cell, leaving subsequent occurrences unchanged.

Mode B: Append Field(s) to Record (Lookup Enrichment Mode)

Preserves the original text in F and appends one or more reference columns selected from R to the right side of the record.

Unmatched Record Handling: If a record in F does not match any search value in R , the record is still passed downstream , and the newly appended columns are populated with [Null] .

4. Deep Comparison: Find Replace Tool vs. Join Tool

Understanding when to use Find Replace instead of Join is a cornerstone of the Core Certification exam:

  • Feature / Behavior Find Replace Tool Join Tool
  • Input Anchors 2 ( F Find, R Replace) 2 ( L Left, R Right)
  • Output Anchors 1 Output Anchor 3 Output Anchors ( L , J , R )
  • Search Key Data Types String data types only Any compatible data types (Numeric, String, Date)
  • Matching Logic Substring, Beginning, or Full Field Exact key equality only
  • Unmatched Records Always retained in output (Appends receive Null ) Partitioned into L or R unjoined anchors

Output Record Count Always equals input row count of F Can shrink (unjoined rows) or expand (Cartesian duplicates)

  • Word Boundary Filter Supported ("Match Whole Word Only") Not supported (exact match only)
  • 5. Practical Use Cases in Data Workflows USE CASE 1: STANDARDIZING ABBREVIATIONS IN TEXT (Replace Mode)

Primary Data (F): Lookup Table (R): Find Replace Output:

  • [Address] [Abbr] [Expansion] [Address]
  • "100 Main St, Apt 4B" + "St" "Street" --> "100 Main Street, Apartment 4B"
  • "500 Elm Blvd" "Apt" "Apartment" "500 Elm Boulevard"
  • "Blvd" "Boulevard"

USE CASE 2: DICTIONARY CODE LOOKUP (Append Mode)

Primary Data (F): Lookup Table (R): Find Replace Output:

  • [CustID] [TypeCode] [Code] [Category] [CustID] [TypeCode] [Category]
  • 101 "RET" + "RET" "Retail" --> 101 "RET" "Retail"
  • 102 "CORP" "CORP" "Corporate" 102 "CORP" "Corporate"
  • 103 "GOV" 103 "GOV" [Null]
  • 6. High-Yield Exam Traps & Best Practices

Warning

Exam Trap: Numeric Fields in Find Replace If you attempt to match numeric customer IDs or transaction amounts using Find Replace, the fields will not appear in the configuration dropdowns. You must convert numeric fields to V_String using an upstream Select tool before connecting to F or R .

Important

Exam Trap: Unchecked "Match Whole Word Only" When replacing short abbreviations like state codes (e.g., "IN" for "Indiana" ), failing to check "Match Whole Word Only" with "Any Part of Field" will disastrously corrupt words like "MAIN" into "MAIndiana" or "TRAINING" into "TRAIndianag" .

Note

Exam Trap: Record Count Retention Unlike the Join tool (which drops unmatched rows unless unioned), the Find Replace tool never drops records from the F stream . The output record count of a Find Replace tool is guaranteed to equal the input record count of F .

Loading diagram... Find Replace Tool Execution Flow