Share via

Splitting data from one to multiple cells

Anonymous
2025-06-18T08:42:14+00:00

Hi there,

I am trying to split data that is in one column to multiple, but cant seem to get it to work via text to columns.

See example of the how the data is presented below. I am trying to get one fruit per column, rather than all 3, but cant seem to do it - the words all have different spacing and endings which i cant work out the deliniation in the text to columns - my goal is to only have 1 fruit/vege in each row - TIA

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2025-06-18T09:21:09+00:00

Try this formula in cell C2:

=IFERROR(TEXTSPLIT(TEXTJOIN(";",, A2:A24), CHAR(10), ";"), "")

Hope this helps.

Was this answer helpful?

1 person found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-06-30T01:19:22+00:00

    Hi Hans,

    This is only returning the first column of data - and any of the items listed 2nd or third disppear?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2025-06-30T01:18:30+00:00

    This is great and works so well!

    The only thing is that I want to analyse this data, but cant because the label is the formula - is there a way around this?

    Was this answer helpful?

    0 comments
  3. HansV 462.6K Reputation points
    2025-06-18T08:52:12+00:00

    Try the following:

    • Select the column.
    • On the Data tab of the ribbon, click Text to Columns.
    • Select Delimited, then click Next >.
    • Select the Other check box.
    • In the box next to it, press Ctrl+J. This enters an (invisible) line break.
    • Click Finish.

    Was this answer helpful?

    0 comments