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
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.
