Nonprofit request: Formula to split cell contents (number of items and length are inconsistent)

[rtolmach]

Hello everyone. I hope that somebody can help our nonprofit with an Excel formula.

We want to migrate some spreadsheets from Google sheets to Excel. We figured out how to manage all the needed formulas, except one: SPLIT.

Here is a public view link to a sample file, which shows you how we currently do this in Google sheets:

Sample of split formula

Sheet1 Record #,List of causes,cause 1,cause 2,cause 3,cause 4,cause 5,cause 6,cause 7,cause 8,cause 9,cause 10,cause 11,cause 12 101, 102,Cancer Environment Hunger ,Cancer,Environment,Hunger 103,Animal welfare Cancer Climate change Digital divide Education Environment Human Rights Hunger Medica...

docs.google.com

Column B contains, for each record, a list of the record's favorite charitable causes, such as Animal welfare, Cancer, Digital divide, Environment, Human Rights, Hunger, Medical research, etc. A cell in that column might contain no content, one cause, or as many as 16 causes, each on a separate line within that cell. The google delimiter for those line breaks within a cell is "char(10)." (I do not know if Excel uses the same).

In Google sheets, our formula in column C is
=if(len(B2)=0,"",split(B2,char(10)))
The first part just makes sure that we do not get a #VALUE error if the cell is empty.

We are familiar with the Text to Columns menu options, but we need a formula in column C such that everything gets updated automatically whenever the content of column B is changed; we cannot do it manually each time.

We welcome and appreciate your suggestions.

Thank you very much!

 

[Eric W]

Welcome to the MrExcel forum!

Excel 365 has a new function called TEXTSPLIT. You should just be able to replace SPLIT with TEXTSPLIT in your formula. If your version of 365 doesn't have TEXTSPLIT yet, let us know and there are other options. CHAR(10) is the same in Excel.

 

[shinigamilight]

Split Cell's Vertical Text Into Multiple Columns

I have an excel cell that contains an address. I want the address to be split into separate cells: Address City State Zip What's unique is these items are stacked vertically, otherwise I would ...

stackoverflow.com

 

[rtolmach]

@Eric W Wow, thank you very much. I had spent hours googling for a solution before posting here. Your solution works beautifully. We could not be more appreciative.