Extracting Username And Domain From Email Fields In Sharepoint Using Calculated Columns

The Problem of Unstructured Email Data

Email addresses contain useful identity data about users but this information is typically all contained together in a single text string that is not easily accessible for reporting and analytics. For example, an email address like [email protected] contains the username “john.doe” and the domain “contoso.com” buried within the single email string.

In order to leverage the identity information within email addresses for business insights, we need a way to systematically extract out the username and domain from the email text string into separate fields that can be used independently in views, searches, filters, and reports.

How Calculated Columns Can Help

Calculated columns in SharePoint provide a way to transform and extract data buried within strings into accessible, usable fields. By using formulas, calculated columns can parse apart an email address string to isolate and output just the username portion and just the domain portion into separate columns.

Table of Contents

Because calculated columns operate at the list or library level, these transformed username and domain fields can then be surfaced in views, column formatting, and reports to enable better filtering, grouping, and business intelligence.

Step-by-Step Guide

Here is how to use calculated columns to extract the username and domain from an email field in SharePoint:

Get the Position of the “@” Symbol

The first step is to locate the central “@” sign that separates the username on the left from the domain on the right. We can get the numeric position of this delimiter using the FIND function. Here is an example formula:

FIND("@",Email)

For a sample email address like [email protected], this formula would return the number 9, representing the “@” symbol’s position as the ninth character in the string.

Use Left() and Right() to Split Email into Parts

Armed with the position of the central “@” delimiter, we can now split apart the email string into its constituent username and domain parts using the LEFT and RIGHT functions:

LEFT extracts text starting from the far left up to the specified delimiter position.
RIGHT extracts text starting from the delimiter position all the way to the end.

Here is an example pair of formulas to output the split username and domain into separate calculated columns:

LEFT([Email],FIND("@",[Email])-1)
RIGHT([Email],LEN([Email]) - FIND("@",[Email]))

The key is taking the LEFT or RIGHT of the email string using the “@” symbol position as the splitting point.

Output Username and Domain into Columns

As the final step, the extracted username and domain formulas can be output into their own dedicated calculated columns to split them apart, such as:

Username – Stores the parsed username value from the LEFT formula.
Domain – Stores the parsed domain value from the RIGHT formula.

This transforms the single email address string into separate accessible fields for reporting and analytics in views, filters, and charts.

Formula Examples

Here are more detailed examples for extracting the username and domain parts into calculated columns from a starting Email text field.

Getting the Username

Use LEFT combined with FIND to extract the username part, up until but not including the “@” symbol:

LEFT([Email],FIND("@",[Email])-1)

Breaking this down:

FIND(“@”,[Email]) gets position of “@” in the email string.
Subtracting 1 stops before including the “@” symbol itself.
LEFT extracts start through the @ minus 1 position.

So for [email protected], it would return “john.doe”.

Getting the Domain

Use RIGHT starting from the “@” symbol position to the end of the string:

  
RIGHT([Email],LEN([Email]) - FIND("@",[Email]))

Breaking this down:

FIND(“@”,[Email]) gets position of “@” in the email string.
LEN([Email]) gets full length of email string.
Subtracting the two gets length from “@” to end.
RIGHT extracts start at “@” through to end of string.

So for [email protected], it would return “contoso.com”.

Adding Calculated Columns to Views and Reports

While calculated columns do the heavy lifting of data transformations, they need to be surfaced properly in interfaces and reports to be usable:

Make Data Usable in List Views

Add the new Username and Domain calculated columns to the default All Items view, column sorting, filtering etc to make visible for users.

Leverage for Filtering and Reporting

Expose the parsed username and domain fields in reporting tools like Power BI and Excel. Use them for filtering, grouping and dashboarding.

Limitations and Considerations

Some limitations when leveraging calculated columns for email parsing:

Handle Variable Email Formats

Formulas may need additional logic to handle irregular formats like:

“John Doe” [email protected]
[email protected]

May require additional string manipulation to trim inconsistencies.

Large Lists Impact Performance

Calculated columns run formulas against every item, so extremely large lists with millions of records may see degraded performance.

Consider moving parsing to data import time for big data scenarios.

Summary

Extracting richer identity data from email address strings is enabled by SharePoint’s built-in capabilities for data transformations:

Calculated columns empower parsing email text into constituent fields using formulas.
Split apart into usable username and domain components.
Surface new data to enhance filtering, reporting and drive better insights.

You can unlock the value in unstructured strings by converting them into structured data using the techniques outlined here. What other complex parsing problems could calculated columns help solve for your business?