LET Function in Excel: A Name Parsing Example

The LET function is one of the most exciting formulas to be added to Excel in 2020.

This formula allows a spreadsheet maker the ability to create names for calculations (within a cell). These names not only make your formulas easier to read, but faster as well!

Prerequisites

The LET function is available in the following versions of Excel:

• Office 365
• Office 365 for Mac
• Excel Online

Unfortunately, this function is available only in subscription versions of Excel at this time. Excel 2019 users do not get to take advantage of this exciting new formula!

The Example Data

To demonstrate the LET function in Excel, the task will be to convert a list of names which are in Last Name, First Name format to First Name, Last Name format.

This data can be downloaded at the link below:

Text parsing formulas that will be used in this example include:

• FIND() – Provides the location of a string within another string (or cell).
• LEFT() – Provides a number of characters from the beginning of a text string (or cell).
• RIGHT() – Provides a number of characters from the end of a text string (or cell).
• LEN() – Provides the number of characters in a string (or cell).
• TEXTJOIN() – Combines text from multiple cells, strings or ranges.

The Text Parsing Formula (without the LET function)

Without the LET function, the code for moving a first name to the front is surprisingly long!

=TEXTJOIN(" ",TRUE,RIGHT([@Name],LEN([@Name])-FIND(", ",[@Name])-1),LEFT([@Name],FIND(", ",[@Name])-1))

There are several elements within this formula:

• Locating where the comma is within the text.
• Extracting the data to the right of the comma.
• Extracting the data to the left of the comma.
• Concatenating the first name and last name.

Locating the comma within the text

The first element of the formula is finding the comma. This is done with the FIND() function.

=FIND(",",[@Name])

Finding the comma (in quotes) within the current row of the table ([@Name]) will provide the location of the comma. This will be critical when trying to determine where to start the first and last names!

Extracting data to the right of the comma – First Name

Now that we know where the comma is, we can extract the data to the right of the comma.

Unfortunately, there is no way to directly to get values to the right of a comma. First, we need to get the length of the data in the cell using the LEN() function.

=LEN([@Name])

Then, you can get a number of characters from the right side of the cell by subtracting the comma location from the total length of the cell.

To make sure that there is no blank space before the name, you need to subtract an additional character.

=RIGHT([@Name],LEN([@Name])-FIND(",",[@Name])-1)

Example

• Webb, Roy is 9 characters long.
• The comma is at the 5th position in the string.
• RIGHT(4) provides the last 4 characters; the string ” Roy”
• Subtracting 1 from the RIGHT provides 3 characters; just “Roy”.

Extracting data to the left of the comma – Last Name

Extracting data from the left side of a string is easier than extracting from the right side.

There is no need to determine the length of the text in the cell; using the LEFT() function will return a number of characters from the start of the cell.

The number of characters will be based on the location of the comma. An additional character will need to be subtracted to exclude the comma.

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

Example

• The comma in Brock, Brenda is found at the 6th character.
• LEFT(6) provides the value “Brock,”
• Subtracting 1 in the LEFT formula provides “Brock”

Concatenating First and Last Name

The TEXTJOIN() function allows values to be combined together in a single cell.

To use TEXTJOIN(), you need to provide:

• Characters to be added between names (a space will be used).
• An argument to determine if blank cells should be ignored (not really relevant here – so TRUE is fine)
• The first and last names, separated by a comma.

If helper columns were used to calculate the first and last names, the formula would look like:

=TEXTJOIN(" ",TRUE,[@[First Name]],[@[Last Name]])

However; without helper columns, we’re back to the full formula below.

=TEXTJOIN(" ",TRUE,RIGHT([@Name],LEN([@Name])-FIND(", ",[@Name])-1),LEFT([@Name],FIND(", ",[@Name])-1))

Using the LET function to make the formula easier to read!

The formula above gets quite complex with the number of [@Name] calls and FIND formulas! It is very easy to misplace a comma, or forget a square bracket, causing the formula to break!

While this formula appears longer, it runs faster, and is easier to debug!

=LET(
selectedName, [@Name],
 commaPlace, FIND(",",selectedName),
TEXTJOIN(" ",TRUE,RIGHT(selectedName,LEN(selectedName)-commaPlace-1),LEFT(selectedName, commaPlace-1)))

LET Function in Excel Syntax

The LET function works by creating pairs of names and values, which are used later within the LET function.

=LET(
selectedName, [@Name],

In the example above, the word “selectName” can be used in place of anywhere in a formula where [@Name] would have been used. This makes it easier to avoid making errors with square brackets!

=LET(
selectedName, [@Name],
 "Name: "& selectedName)

When used in combination with a formula like FIND(), it can return the location of a comma. As a result, the name commaPlace can be used in place of a FIND() function throughout the formula!

=LET(commaPlace, FIND(",",[@Name]),commaPlace)

Multiple variables can also be used in the same LET formula; in the example below, the selectedName variable is used later when creating the commaLocation name variable within the LET formula!

=LET(selectedName, [@Name], commaLocation, FIND(",",selectedName),TEXTJOIN(" ",TRUE,"Name: ",selectedName,"/ Comma Location: ",commaLocation))

In addition, variables that have been declared earlier in the formula will appear as selectable later in the LET formula!

Converting the text parsing formula using LET.

The starting point for the conversion of the formula using LET is the initial formula where no variables have been created.

=TEXTJOIN(" ",TRUE,RIGHT([@Name],LEN([@Name])-FIND(", ",[@Name])-1),LEFT([@Name],FIND(", ",[@Name])-1))

Converting the Name field to a variable

The Name field from the table is used 5 times in the formula, and could lead to some confusion; especially if first and last names are added later.

After copying the TEXTJOIN formula to a new cell, create a new variable. We will name the variable selectedName in this case.

=LET(
selectedName, [@Name], 
TEXTJOIN(" ",TRUE,RIGHT([@Name],LEN([@Name])-FIND(", ",[@Name])-1),LEFT([@Name],FIND(", ",[@Name])-1)))

Next, replace the [@Name] parameter with the selectedName variable.

=LET(selectedName, [@Name], TEXTJOIN(" ",TRUE,RIGHT(selectedName,LEN(selectedName)-FIND(", ",selectedName)-1),LEFT(selectedName,FIND(", ",selectedName)-1)))

Replacing the comma find function with a variable

Within the TEXTJOIN function, there are still 2 cases where values are being reused. These are the FIND functions which are looking for the comma.

FIND(", ",selectedName)

We can add this FIND() function before the TEXTJOIN() function, and give it a more relevant name; like commaPlace.

=LET(selectedName, [@Name], commaPlace, FIND(", ",selectedName)

It is important that commaLocation comes after selectedName!

LET() functions allow variables to be used later within the function (such as how selectedName was used in commaPlace).

However; the LET function reads from left to right. You can not use a variable before it has been created.

Once the commaPlace has been added, the final formula appears as follows:

=LET(
selectedName, [@Name],
 commaPlace, FIND(",",selectedName),
TEXTJOIN(" ",TRUE,RIGHT(selectedName,LEN(selectedName)-commaPlace-1),LEFT(selectedName, commaPlace-1)))

Things to remember about the LET function

1. Any variables created inside a LET function can only be used within that LET function.
2. Variables created within a LET function can only be used after they have been written.
3. Only use a LET function if you have a calculation that is required more than once.
4. If you need to refer to a cell across multiple formulas, it can be easier to use a named range vs. a LET formula.