LET Function in Origin

Excel like LET function is introduced in Origin 2021. The function assigns values into intermediate variables and uses these variables in an expression for calculation.

LET(name1, value1 [, name2, value2, ] … [name39, value39], expression)[$]

It allows multiple name/value pairs as input and the value can number, function result, expression, etc. Some simple examples:

let(x, 1, x+1)  //assign 1 to x and calculate x+1 so return is 2

let(x,1, y, 2, x+y) //assign 1 as x, 2 as y and calculate x+y so return is 3

Here are some practical examples of using LET function to extract useful information from email addresses. Please also download the project to check the result.

Example 1: Extract Text before @ in Email Address

Column A contains edu email addresses. The length of text before @ varies. So we need to find position of @ in email address and then get text before @

Function: LET(n, find(A,"@"), left(A,n-1))

Explanation:

1. find(A,”@”) function returns the index of letter @ in column A and assign it to variable n.
2. Use expression left(A,n-1) gets the leftmost n-1 characters in column A, which means text before @

Example 2: Extract Text before @ for EDU Email Addresses only

There are edu and com emails in column A. We want to extract text before @ of edu email addresses only. For com email addresses, we want to show missing values.

Function:  LET(n, find(A,"@"), IF(right(A,3) == "com",0/0,left(A,n-1)))

Explanation:

1. find(A,”@”) function returns the index of letter @ in column A and assign it to variable n.
2. the expression in this LET is actually an IF(condition, value_if_true, value_if_false) function. If the last 3 chars in email is com, return missing value 0/0, otherwise  return the text before @.

Example 3: Extract University Part of EDU Email Addresses

University name is between @ and . in email address. So we need to find position of @ and . and then extract text between them.

Function: LET(n1, find(A, "@"), n2, find(A,"."), Mid(A,n1+1, n2-n1-1)$)

Explanation:

1. find(A,”@”) function returns the index of letter @ in column A and assign it to variable n1.
2. find(A,”.”) function returns the index of letter . in column A and assign it to variable n2.
3. mid(A,n1+1, n2-n1-1)$ function returns the text between @ and . in column A.