Text Joining and Parsing

Text Functions: Joining & Parsing - Your Text Toolbox

The Story of the Text Chef

Imagine you’re a chef, but instead of cooking food, you’re cooking words. Sometimes you need to mix ingredients together (like making a smoothie). Other times you need to chop things apart (like slicing a pizza). Excel’s text functions are your kitchen tools!

Meet Your Tools

graph TD
    A["Text Functions"] --> B["JOINING Tools"]
    A --> C["PARSING Tools"]
    B --> D["CONCAT"]
    B --> E["TEXTJOIN"]
    B --> F["TEXT"]
    C --> G["VALUE"]
    C --> H["TEXTBEFORE"]
    C --> I["TEXTAFTER"]
    C --> J["TEXTSPLIT"]

1. CONCAT - The Simple Glue

What is it?

CONCAT is like tape. It sticks words together, one after another.

Think of it like this:

You have three LEGO blocks: “Hello”, " ", “World” CONCAT snaps them together: “Hello World”

How to use it:

=CONCAT(text1, text2, text3, ...)

Real Example:

Another Example:

=CONCAT("I", " ", "love", " ", "Excel")

Result: I love Excel

Why use it?

• Combining first and last names
• Building product codes
• Creating sentences from parts

2. TEXTJOIN - The Smart Glue

What is it?

TEXTJOIN is CONCAT’s smarter sibling. It adds a separator between each piece automatically!

Think of it like this:

You’re making a necklace. Each bead is a word, and the separator is the string between beads.

How to use it:

=TEXTJOIN(delimiter, ignore_empty, text1, text2, ...)

• delimiter: What goes between each piece (like a comma)
• ignore_empty: TRUE = skip blanks, FALSE = include blanks

Real Example:

=TEXTJOIN(", ", TRUE, A1:C1)

Result: Apple, Banana, Cherry

Power Example with Blanks:

=TEXTJOIN("-", TRUE, A1:C1)

Result: Red-Blue (skipped the empty cell!)

=TEXTJOIN("-", FALSE, A1:C1)

Result: Red–Blue (kept the empty spot!)

3. TEXT - The Costume Designer

What is it?

TEXT takes a number and dresses it up as text, wearing whatever costume (format) you choose!

Think of it like this:

The number 1000 is shy. TEXT gives it a fancy outfit: “$1,000.00”

How to use it:

=TEXT(value, format_code)

Common Format Codes:

Real Examples:

=TEXT(1234.5, "$#,##0.00")

Result: $1,234.50

=TEXT(TODAY(), "dddd, mmmm d, yyyy")

Result: Wednesday, March 15, 2023

Why use it?

• Making numbers look pretty in sentences
• Formatting dates for reports
• Creating consistent displays

4. VALUE - The Reverse Magic

What is it?

VALUE does the opposite of TEXT. It takes text that looks like a number and turns it into a real number!

Think of it like this:

Someone wrote “42” on paper. VALUE turns that paper into actual counting blocks you can do math with.

How to use it:

=VALUE(text)

Real Examples:

=VALUE("123")

Result: 123 (now it’s a real number!)

=VALUE("45.67")

Result: 45.67

=VALUE("123") + 10

Result: 133

When you need it:

• Data imported as text
• Numbers from web pages
• Values pasted from other programs

Warning:

VALUE only works with text that looks like a number!

=VALUE("hello")

Result: #VALUE! error (can’t turn letters into numbers)

5. TEXTBEFORE - The Left Cutter

What is it?

TEXTBEFORE grabs everything before a specific character or word.

Think of it like this:

Your email is “john@gmail.com”. TEXTBEFORE can grab “john” (everything before the @).

How to use it:

=TEXTBEFORE(text, delimiter)

Real Examples:

=TEXTBEFORE("john@gmail.com", "@")

Result: john

=TEXTBEFORE("apple-banana-cherry", "-")

Result: apple (stops at first dash)

Finding the Second Occurrence:

=TEXTBEFORE("apple-banana-cherry", "-", 2)

Result: apple-banana (stops at second dash)

Practical Uses:

• Extracting usernames from emails
• Getting first names from “First Last” format
• Parsing file names

6. TEXTAFTER - The Right Cutter

What is it?

TEXTAFTER is TEXTBEFORE’s partner. It grabs everything after a specific character.

Think of it like this:

Your email is “john@gmail.com”. TEXTAFTER grabs “gmail.com” (everything after the @).

How to use it:

=TEXTAFTER(text, delimiter)

Real Examples:

=TEXTAFTER("john@gmail.com", "@")

Result: gmail.com

=TEXTAFTER("apple-banana-cherry", "-")

Result: banana-cherry

Finding After Second Occurrence:

=TEXTAFTER("apple-banana-cherry", "-", 2)

Result: cherry

Practical Uses:

• Getting domain from email
• Extracting file extensions
• Getting last name from full name

7. TEXTSPLIT - The Ultimate Slicer

What is it?

TEXTSPLIT is like a pizza cutter. It takes one long text and cuts it into separate pieces!

Think of it like this:

You have a sentence: “apple,banana,cherry” TEXTSPLIT cuts it at every comma, giving you three separate cells!

How to use it:

=TEXTSPLIT(text, col_delimiter, [row_delimiter])

Real Examples:

Split by comma (into columns):

=TEXTSPLIT("apple,banana,cherry", ",")

Result: | apple | banana | cherry |

Split by space:

=TEXTSPLIT("Hello World Excel", " ")

Result: | Hello | World | Excel |

Split into rows AND columns:

=TEXTSPLIT("a,b,c;d,e,f", ",", ";")

Result: | a | b | c | | d | e | f |

Super Power:

TEXTSPLIT creates a spill - it automatically fills multiple cells!

Quick Comparison

The Flow: When to Use What?

graph TD
    A["What do you need?"] --> B{Combine text?}
    B -->|Simple join| C["CONCAT"]
    B -->|With separator| D["TEXTJOIN"]
    A --> E{Format a number?}
    E -->|Yes| F["TEXT"]
    A --> G{Convert text to number?}
    G -->|Yes| H["VALUE"]
    A --> I{Extract part of text?}
    I -->|Before something| J["TEXTBEFORE"]
    I -->|After something| K["TEXTAFTER"]
    I -->|Split into pieces| L["TEXTSPLIT"]

Real-World Combo Example

You have: “Smith, John - Employee #1234”

Get Last Name:

=TEXTBEFORE("Smith, John - Employee #1234", ",")

Result: Smith

Get First Name:

=TEXTBEFORE(TEXTAFTER("Smith, John - Employee #1234", ", "), " -")

Result: John

Get Employee Number:

=VALUE(TEXTAFTER("Smith, John - Employee #1234", "#"))

Result: 1234 (as a number!)

You Did It!

Now you’re a Text Chef! You can:

• Join text with CONCAT and TEXTJOIN
• Format numbers beautifully with TEXT
• Convert text to numbers with VALUE
• Extract parts with TEXTBEFORE and TEXTAFTER
• Split text into pieces with TEXTSPLIT

Go practice - your data is waiting to be transformed!