Split Full Names, Parse Codes, Join Columns — Text Manipulation with LEFT, MID, FIND, SUBSTITUTE and TEXTJOIN in C#

Business data is full of strings that need to be taken apart or put back together. Split a full name into first and last. Pull the domain out of an email address. Break a product code like TK-2026-0042 into its parts. Or the reverse: join a handful of columns into one comma-separated line.

In C# that’s one call to string.Split — but if you want the results in a form users can see on screen and fix row by row, spreadsheet formulas are the better tool. Excel’s workhorses here are LEFT, MID, FIND, SUBSTITUTE, and TEXTJOIN, and ReoGrid added all of them in V4.5. This article walks through the recipes that come up constantly in real business data, the traps hiding in them, and how to run the same formulas inside a WinForms / WPF app.

This article is part of a series on running Excel’s workhorse functions in .NET. See also VLOOKUP vs HLOOKUP vs XLOOKUP for looking values up, MATCH vs XMATCH for finding positions, and EDATE / EOMONTH / WORKDAY / NETWORKDAYS for date arithmetic.

The toolbox — six functions and one operator

The mental model is simple: the cutting functions (LEFT / RIGHT / MID) take positions as arguments, and the position is supplied by FIND and LEN. FIND answers “where do I cut?”; LEFT / MID do the cutting. That division of labor is the basic shape of almost every text formula.

Recipe 1 — split a full name into first and last

The classic. Split John Smith at the space:

A2: John Smith

=LEFT(A2, FIND(" ", A2) - 1)              → John   (first name)
=MID(A2, FIND(" ", A2) + 1, LEN(A2))      → Smith  (last name)

FIND(" ", A2) returns the position of the space (5), LEFT takes everything before it, MID takes everything after. Passing LEN(A2) as MID’s length is the standard “the rest of the string” idiom — anything past the end is trimmed automatically, so you never need to compute the exact remaining length.

Real data has stray spaces — and rows with no space at all

On a real imported list, this formula breaks in two ways: rows with leading/trailing or doubled spaces, and rows where someone typed the name with no space at all (FIND returns an error). The robust version adds a normalization column and wraps the splits in IFERROR:

B2: =TRIM(A2)                                            ← clean up stray spaces
C2: =IFERROR(LEFT(B2, FIND(" ", B2) - 1), B2)            ← first (fall back to whole name)
D2: =IFERROR(MID(B2, FIND(" ", B2) + 1, LEN(B2)), "")    ← last

Rows that can’t be split mechanically stay visible on screen for a human to fix instead of being silently mangled — which is exactly why this kind of cleanup is worth doing in a sheet rather than in code.

One note for CJK data: Japanese name lists routinely mix full-width spaces (　, U+3000) with regular ones, so normalize with SUBSTITUTE(A2, "　", " ") before FIND. If your data has wider full-width/half-width problems (katakana, digits, symbols), see our article on normalizing full-width and half-width characters — ReoGrid V4.5 also supports Excel’s ASC / JIS functions for that.

Recipe 2 — extract the domain from an email address

Everything after a marker character. FIND locates the @, MID takes the rest:

A2: [email protected]

=MID(A2, FIND("@", A2) + 1, LEN(A2))      → example.com
=LEFT(A2, FIND("@", A2) - 1)              → alice  (the local part)

The same shape handles “everything after the last backslash” style problems, extracting the currency code from USD 1,280.00, and so on. If the marker might be missing, wrap it in IFERROR just like the name split.

Recipe 3 — parse a delimited code

A fixed-format code like TK-2026-0042 (site – year – sequence). The first hyphen is easy; the trick is the second one. FIND’s third argument (start position) lets you search “after the first hit”:

A2: TK-2026-0042

=LEFT(A2, FIND("-", A2) - 1)                              → TK    (site)
=MID(A2, FIND("-", A2) + 1,
     FIND("-", A2, FIND("-", A2) + 1) - FIND("-", A2) - 1) → 2026  (year)
=MID(A2, FIND("-", A2, FIND("-", A2) + 1) + 1, LEN(A2))   → 0042  (sequence)

If the middle formula makes your eyes water, that’s a normal reaction. For codes with three or more delimiters there’s a much cleaner trick built on SUBSTITUTE’s fourth argument (replace only the Nth occurrence): turn the Nth delimiter into a marker character, then find the marker.

=FIND("|", SUBSTITUTE(A2, "-", "|", 2))    → position of the 2nd hyphen

And a practical judgment call worth stating: if the code is truly fixed-width (2-char site + 4-digit year + …), skip FIND entirely and cut directly with MID(A2, 4, 4).

Joining — TEXTJOIN vs CONCAT vs &

The reverse direction has three tools:

=C2 & " " & D2                       → John Smith           (fastest for a few fixed fields)
=CONCAT(A2:E2)                       → everything, no delimiter
=TEXTJOIN(", ", TRUE, A2:E2)         → value1, value2, ...  (whole range, skips blanks)

The one that earns its keep in business apps is TEXTJOIN’s second argument. With TRUE, empty cells are skipped before the delimiter is inserted, so joining an address whose “building” field is blank — or a name with no middle name — never produces , , runs. Typical uses: assembling remark lines, one-column CSV-ish exports, mailing labels.

Traps and current limitations, honestly

• FIND is case-sensitive (same as Excel). The case-insensitive SEARCH is not supported as of V4.5 — normalize with UPPER / LOWER before FIND instead. Wildcards (*, ?) are not supported either.
• Excel 365’s newer TEXTSPLIT / TEXTBEFORE / TEXTAFTER are not supported. The FIND + LEFT / MID combinations in this article are the drop-in substitutes — and they have the bonus of being compatible with older Excel files, too.
• SUBSTITUTE replaces by content; REPLACE replaces by position. “Replace 4 characters starting at position 3” is REPLACE(A2, 3, 4, "****") — masking jobs belong to REPLACE.

Running these formulas inside a C# app

ReoGrid supports this text function family as of V4.5. Here’s an import-cleanup screen in miniature: a pasted name column being split into first / last, with the un-splittable rows left visible for the user. No Office, no Interop.

using unvell.ReoGrid;

var sheet = reoGridControl.CurrentWorksheet;

// headers
sheet["A1"] = new object[,] {
    { "Name (raw)", "Normalized", "First", "Last" }
};

// imported raw data — messy on purpose
sheet["A2"] = "John Smith";
sheet["A3"] = "  Mary   Johnson ";   // stray spaces
sheet["A4"] = "Cher";                // no space → can't split

// formula columns: normalize → first → last
for (int r = 1; r <= 3; r++)
{
    int n = r + 1;
    sheet[$"B{n}"] = $"=TRIM(A{n})";
    sheet[$"C{n}"] = $"=IFERROR(LEFT(B{n}, FIND(\" \", B{n}) - 1), B{n})";
    sheet[$"D{n}"] = $"=IFERROR(MID(B{n}, FIND(\" \", B{n}) + 1, LEN(B{n})), \"\")";
}

The split results are visible cells, so the user can fix the Cher row by hand. When the data is confirmed, collect it back into C# with GetCellData<T>:

string firstName = sheet.GetCellData<string>("C2");   // John
string lastName  = sheet.GetCellData<string>("D2");   // Smith

Once you’re past the interactive stage — data confirmed, heading for the database — switch to string.Split in C# for the bulk work. Exploration and eyeballing in sheet formulas, confirmed bulk processing in C# is the least painful division of labor for this kind of data-cleanup screen.

Summary

• The cutting functions (LEFT / RIGHT / MID) take positions — and FIND / LEN supply them
• Name splitting is FIND(" ", …) at heart, but real data needs a TRIM normalization column and IFERROR fallbacks so broken rows stay visible
• FIND’s third argument (start position) finds the second delimiter; SUBSTITUTE’s fourth argument (Nth instance only) is the cleaner trick for deeper delimiters
• Join a few fixed fields with &; join ranges while skipping blanks with TEXTJOIN(delimiter, TRUE, range)
• SEARCH and the TEXTSPLIT family are not supported as of V4.5 — UPPER + FIND and FIND + MID are the substitutes
• ReoGrid V4.5 supports all of the above, so the same formulas run inside your WinForms / WPF app — no Office required

See what’s new in ReoGrid 4.5 / Start a 30-day trial

Read next

• Normalizing full-width and half-width characters in C# business apps
• VLOOKUP vs HLOOKUP vs XLOOKUP — choosing a lookup function
• MATCH vs XMATCH — the functions that return positions
• EDATE / EOMONTH / WORKDAY / NETWORKDAYS — business date calculations
• Formula and calculation engine — the full list of built-in functions
• ReoGrid 4.5 release — 47 Excel-compatible functions and point-mode formula editing