Script To Take Column Description & Create Separate Columns From It

Copper Contributor

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 backward slashes.  I am hoping to catch the eye of an Excel guru who might know how to run a script to parse that info out into their own columns.  Thank you in 

 

2024-02-15_10-09-23.png

3 Replies

@Matthew_Hoff 

It does not need to have/run a script in order to achieve that.
If you're familiar with Text-to-column or Delimiter, it can be done by using/doing it.

Text to Columns in Excel (In Simple Steps)

@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,

image.png

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

image.png

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:

@Rr_  That is exactly what I was looking for.  Thank you so much!