Forum Discussion

Matthew_Hoff's avatar
Matthew_Hoff
Copper Contributor
Feb 15, 2024

Script To Take Column Description & Create Separate Columns From It

That headline might be confusing & so hopefully my image will make it clear.  We have an exported report from an online document management platform.  The first column is a folder path separated by b...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Feb 17, 2024

Matthew_Hoff 

Using Excel 365 it is also possible to split the folder name and path using a formula but it is nowhere near as easy as it should be!  What one might expect is to use the built in function TEXTSPLIT.  Unfortunately 

= TEXTSPLIT(folderNameAndPath,"/")

returns only the root folder of each path,

which is useless.  One can, however, write one's own Lambda function

and make it work as the built-in function should.

TEXTSPLITλ(string, separator, [length])
= LET(
    ℓ, IF(ISOMITTED(length), 32, length),
    fixedwidth, MAP(string, TOFIXEDλ(separator, ℓ)),
    N, MAX(LEN(fixedwidth)) / ℓ,
    TRIM(MID(fixedwidth, SEQUENCE(, N, 1, ℓ), ℓ))
  )

which, in turn, calls a Lambda function TOFIXEDλ to convert the "/"-separated strings to fixed width strings.  All this because the MID function will return an array of arrays

TOFIXEDλ(sep, len)

= LAMBDA(string, 
    LET(
      items, TEXTSPLIT(string, sep),
      CONCAT(LEFT(items & REPT(" ", len), len))
    )
  )

This Lambda function called TEXTSPLIT but with only a single string.  In order to return a scalar, TOFIXEDλ pads the substrings with space characters to give a fixed length string and concatenates the result.

 

It works, but should the user really be forced to go to such lengths to meet such a simple requirement?

:facepalm:

Resources