Function Problems

Excel LAMBDA: Create a Custom Concatenation Function

Bushra ParveenBushra Parveen Nov 12, 2026 868 views

Question details

The user needs to combine text from selected cells into customized messages with a reusable formula instead of repeatedly entering a long concatenation formula or creating a VBA function.

Excel LAMBDA: Create a Custom Concatenation Function
Product
Microsoft Excel 365
Device & OS
not provided
Scenario
Create a custom text-concatenation function that accepts a cell range and additional values, then reuse it across worksheet rows.
Observed behavior
A long text formula would otherwise need to be entered repeatedly, while Excel 365 provides LAMBDA, LET, and dynamic-array functions for creating reusable worksheet functions.
Before you start

Confirm that Excel 365 is available and that the workbook supports LAMBDA, LET, and TAKE. Prepare the cell range containing the words or text fragments and identify the name and year values you want to insert.

Solution 1Recommended

Create a reusable Excel LAMBDA concatenation function

Use this method in Excel 365 when you want to save a custom text function and call it by name throughout the workbook.

LAMBDA lets you create a reusable worksheet function without VBA. The example takes the first and last values from a selected range, then places a name and year between them.

1
Open Name Manager

In Excel, open the Formulas tab and select Name Manager, then click New to define a workbook-level function.

2
Name the function

In the Name field, enter StudentYear. In the Refers to field, enter =LAMBDA(words,name,year,LET(first,TAKE(words,1),last,TAKE(words,-1),first&" "&name&" "&last&" "&year)).

3
Save the function

Click OK in the New Name dialog, then close Name Manager to make the custom function available in the workbook.

4
Call the function in a worksheet

Select a result cell and enter =StudentYear(A2:A4,B2,C2), replacing A2:A4 with the text range, B2 with the name, and C2 with the year. Press Enter to return the combined message.

Reusable worksheet function created: You can use StudentYear in other cells in the same workbook without retyping the LAMBDA definition.
Free Microsoft Office alternative

Build reusable spreadsheet text formulas with WPS Office

WPS Office provides a free, lightweight spreadsheet alternative for opening and editing Excel workbooks. Its familiar interface supports common formula workflows and helps users migrate while preserving Microsoft Excel .xlsx compatibility.

  1. 1. Open the workbook: Launch WPS Office, open WPS Spreadsheet, and select the Excel .xlsx workbook from the home screen.
  2. 2. Create the formula: Click the target result cell and enter the supported concatenation formula using cell references, then press Enter.
  3. 3. Fill results down: Use the cell fill handle or copy and paste the formula into the remaining rows to generate each customized message.
Free and lightweight spreadsheet software for creating customized text resultsFamiliar worksheet interface for entering, copying, and managing formulasSupports Microsoft Excel .xlsx and .xls workbook formats for easier migrationUseful for organizing source cells and building repeatable concatenation workflows
microsoft office alternative - wps office

Frequently Asked Questions

Can Excel LAMBDA replace a VBA user-defined concatenation function?

Yes. In Excel 365, LAMBDA can create reusable worksheet functions without VBA. Save the formula through Formulas > Name Manager > New, then call the saved name in worksheet cells.

How do I combine the first and last values from a selected range?

Use TAKE inside LET, such as =LAMBDA(words,name,year,LET(first,TAKE(words,1),last,TAKE(words,-1),first&" "&name&" "&last&" "&year)). The formula returns the first range value, the name, the last range value, and the year.

What formula creates a student message from two cells?

If the student name is in A2 and the year is in B2, enter ="Student with name "&A2&" is now in year "&B2 in the result cell, then fill it down for additional rows.

Why does the LAMBDA formula show a function or name error?

Check that you are using Excel 365 with LAMBDA, LET, and TAKE support, and confirm that the custom function was saved in Formulas > Name Manager. Also verify that the function name and cell references match the saved definition.