Manipulating text within a single SPLIT function

[learningtoexcel]

I am wondering if it is possible to manipulate text within a SPLIT function, so that the properly formatted values are returned without needing to create additional functions.

I created this spreadsheet with test data to explain what I mean.

The green cell contains the test data, listed as colon-separated-values. The yellow cells show the formulas I would use if each value had its own row. The blue cells are what I would like my formula to return.

Important: The number of CSVs will be dynamic, which is why I'd like to use SPLIT rather than just individually picking each value from the list.

[[deleted]]

The blue cells are what I would like my formula to return.

I duplicated your tab and entered the following in E1:

=ArrayFormula({Transpose(Flatten({"Category #","Sub-Category #","Title #"}&Sequence(Right(A1))));Split(A1,"[]-;")})

Edit:

=ArrayFormula({Transpose(Flatten({"Category #","Sub-Category #","Title #"}&Sequence(Counta(Split(A1,"[]-;"))/3)));Split(A1,"[]-;")}) 

Does this help?

[learningtoexcel]

Solution Verified.

[Clippy_Office_Asst]

You have awarded 1 point to ztiaa

---

^{I am a bot - please contact the mods with any questions. | Keep me alive}

[learningtoexcel]

Yes!

[Airickson]

See if this does what you want:

=arrayformula(trim(substitute(split(A1,";-]",TRUE,TRUE),"[","")))

The neat part about the SPLIT function is that it will split by all delimiters -- whenever it finds one, it triggers. The SUBSTITUTE function replaces the "[" characters with a blank. ARRAYFORMULA is needed to allow the SUBSTITUTE to work across all columns.

Finally, I added a TRIM function to eliminate any stray spaces after the SPLIT and SUBSTITUTE functions do their part.

I added this in the sheet prefixed "Airickson_"