Excel LAMBDA: Create a Custom Concatenation Function
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.

- 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.
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.
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.
In Excel, open the Formulas tab and select Name Manager, then click New to define a workbook-level 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)).
Click OK in the New Name dialog, then close Name Manager to make the custom function available in the workbook.
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.
Use a direct concatenation formula for a simple row result
Choose this option when each message uses values from the same row and you do not need a reusable custom function.
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. Open the workbook: Launch WPS Office, open WPS Spreadsheet, and select the Excel .xlsx workbook from the home screen.
- 2. Create the formula: Click the target result cell and enter the supported concatenation formula using cell references, then press Enter.
- 3. Fill results down: Use the cell fill handle or copy and paste the formula into the remaining rows to generate each customized message.

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.




