SpreadsheetWeb Hub supports Excel's LAMBDA function. The recently released LAMBDA function allows Excel users to build custom, reusable functions without VBA. There are so many use cases of the LAMBDA function. In this article, we will create a LAMBDA Function to split text by a delimiter character. Then we will showcase a SpreadsheetWeb application using this function for text splitting scenarios.

## Text splitting by delimiter algorithm

For those who analyze data in Excel, Text to Column is one of the most useful features. However, it is User Interface feature that requires user to go through several steps to split data manually. You can always write a macro to automate this but it is not as efficient as having an actual worksheet formula. Now thanks to LAMBDA, you can create your own text splitting function.

A common approach is to check each character from start and split when the character matching the predefined *delimiter* is found. However, this method is resource intensive if the string size grows. Rather than detecting the delimiter character, we can replace delimiters with spaces. Then we can divide the string into substrings separated by spaces. After parsing substrings, the trimming will return each substring without spaces around.

### Conventional formula

For our approach the conventional formula to split text by delimiter is like this:

MID(

SUBSTITUTE(<text>, <separator>, REPT(" ", LEN(<text>)),

(SEQUENCE(1, <substring count>) - 1) * LEN(<text>) + 1,

LEN(<text>)))

- The
**SUBSTITUTE**function replaces the separator with a space character. - Since any part of the whole string cannot include more characters than the original string, the character count can be used to generate a “long enough” padding string. This can be handled with the
**REPT**function which simply repeats the given string a given number of times. The**LEN**function can provide the character count of the given text. - Once replacement takes place, each data can be separated into string groups thanks to the
**MID**The**MID**function can parse a string from a text value based on a start point and character length. - The trick behind calculating the starting point and the number of characters is to use the length of the full string. When the text with spaces is separated into chunks equal to the original character length, each chunk will contain a data piece along with cushion spaces.
- The formula for calculating the start points includes the
**SEQUENCE**function to generate numbers starting from 1 to the maximum column number. The array returning from the**SEQUENCE**function causes the**MID**function to return an array as well. - If you want to calculate the column count dynamically, you can count the delimiter character in the rows and use that number in the formula: How to count a specific character in a string in Excel
- Finally, the
**TRIM**function removes the cushion space characters around the separated data.

In the example above, the number of substrings is static. If the number is unknown, the formula gets longer and harder to maintain.

### LAMBDA Function

The **LAMBDA** function is a game-changer feature that allows you to create your own functions without any VBA, macro, or JavaScript knowledge. Since its unique nature, the function has a unique use case.

- A
**LAMBDA**formula should be written in a named range. That named range's name will be your custom function's name. - You need to type parameter names before the function itself. Such as, a and b are the parameters and the latter operation is the function itself: =LAMBDA(a, b, a-b)
**LAMBDA**functions can be called recursively.

For detailed information, please visit the following page: Excel LAMBDA Function

**LAMBDA**function requires a Microsoft 365 subscription.

### LET Function

Excel's **LET** function has also been released together with **LAMBDA** to simplify formula writing and maintainability. The **LET **Function allows you to define names within the scope of a formula. Thus, you can use in-formula names instead of long expressions. For example, we can give *LEN(<text>)* expression a name and use it instead. This feature removes the necessity to update each instance of expression when a change is needed.

### Splitting text by delimiter with LAMBDA Function

A splitting text function would have 2 must-have arguments:

- Text to be split
- Separator (or delimiter) character

Once these argument names are defined, the actual formula can be written. Please pay attention to how the **LET** function defines and uses *length* names as well.

LET(

length, LEN(text),

TRIM(

MID(

SUBSTITUTE(text, separator, REPT(" ", length)),

(SEQUENCE(1, MAX(length - LEN(SUBSTITUTE(text, separator, ""))) + 1) - 1) * length + 1,

length

)

)

)

)

Since we called our custom function **SplitText**, here is how it works:

## Publishing your model as a web application

There are many use cases of text splitting in a web application. In this example, we have built a SpreadsheetWeb application using SplitText LAMBDA formula above an an Excel model. User can copy and paste a large coordinate data in degrees, minutes, and seconds format. The application will convert it to a more common latitude and longitude format and create map displaying each location.