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

rtolmach

New Member
Joined
Nov 25, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!
 
Last edited by a moderator:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
 
Upvote 0
Solution
@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.
 
Upvote 0

Forum statistics

Threads
1,223,264
Messages
6,171,085
Members
452,378
Latest member
Hoodzy01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top