google-sheetstextsplitgoogle-sheets-formulaboolean

How to prevent the SPLIT function from converting text to non-text in Google Sheets

I have a comma-delimited list of words in a single spreadsheet cell, such as this:

hello, True, Franklin, true, goodbye

The SPLIT() function extracts each word into its own cell. Except that it turns the word "true" into the Boolean value TRUE:

hello TRUE Franklin TRUE goodbye

I want to split each word into its own cell, but to create only text values, and to maintain the original case of each word:

hello True Franklin true goodbye

Any ideas? Here is a demonstration spreadsheet showing the result of SPLIT, as compared to the expected/desired result.

Solution

  • You can use:

    =SPLIT(SUBSTITUTE("'"&C1,", ",", '"),", ")

    More generic:

    =LET(del,", ",SPLIT(SUBSTITUTE("'"&C1,del,del&"'"),del))