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:
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!
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
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!
Last edited by a moderator: