Learn Advanced Formula Combinations | Scenario Modeling and Interactive Decision Systems

Swipe to show menu

Many real-world datasets contain multiple pieces of information inside a single text field. In this chapter, combine text functions to extract structured information dynamically from email addresses.

LEN Structure

=LEN(text)

text: evaluated string.

LEN returns the total number of characters in a string.

FIND Structure

=FIND(find_text, within_text, [start_num])

find_text: character or text being searched;
within_text: evaluated string;
[start_num]: optional starting position.

FIND returns the position of a character inside a string.

LEFT Structure

=LEFT(text, num_chars)

text: evaluated string;
num_chars: number of characters extracted from the left side.

MID Structure

=MID(text, start_num, num_chars)

text: evaluated string;
start_num: extraction starting position;
num_chars: number of characters extracted.

Step 1 Open the Customers_Clean Sheet

Move to:

Customers_Clean

The email data has already been standardized using SUBSTITUTE.

Step 2 Create the Extraction Columns

Add the following columns:

Email_Username
Email_Domain
Email_Valid

Step 3 Locate the @ Symbol

Type:

=FIND("@", Customers_Clean[@Email_Clean])

The formula returns the position of the @ delimiter.

Step 4 Extract the Email Username

Type:

=LEFT([@[Email_Clean]], FIND("@", [@[Email_Clean]]) - 1)

FIND(...) - 1: identifies the last character before @;
LEFT(...): extracts the username portion.

Step 5 Extract the Email Domain

Type:

=MID([@[Email_Clean]], FIND("@", [@[Email_Clean]]) + 1, LEN([@[Email_Clean]]) - FIND("@", [@[Email_Clean]]))

FIND(...) + 1: starts extraction after @;
LEN(...) - FIND(...): calculates remaining character count.

The formula dynamically extracts domains regardless of length.

Step 6 Test Dynamic Parsing

Modify several email addresses with:

Different username lengths;
Different domain lengths;
Different domain providers.

Confirm that the extraction formulas continue working dynamically.

Everything was clear?

Thanks for your feedback!

Section 5. Chapter 4

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Section 5. Chapter 4

Advanced Formula Combinations

LEN Structure

FIND Structure

LEFT Structure

MID Structure

1. What is the main reason `LEN` is used in text extraction formulas like `MID`?

2. Why is `FIND` preferred over manual positioning when extracting parts of an email?

3. In the email domain extraction formula, what is the role of `LEN - FIND("@", …)`?

Advanced Formula Combinations

LEN Structure

FIND Structure

LEFT Structure

MID Structure

1. What is the main reason LEN is used in text extraction formulas like MID?

2. Why is FIND preferred over manual positioning when extracting parts of an email?

3. In the email domain extraction formula, what is the role of LEN - FIND("@", …)?

1. What is the main reason `LEN` is used in text extraction formulas like `MID`?

2. Why is `FIND` preferred over manual positioning when extracting parts of an email?

3. In the email domain extraction formula, what is the role of `LEN - FIND("@", …)`?