locked
Split columns by CR in excel cells

    Question

  • Hello,

    I don't find the way to split columns by Carriage Return from an Excel files.

    My column have multi-rows cells and i want to create one row for each rows in the cells.

    Can you help me ?

    Thanks.

    Simon P.

    Monday, June 10, 2013 7:40 AM

Answers

  • Try

    = Table.SplitColumn(Source,"Column1",Splitter.SplitTextByDelimiter("#(cr)"))

    or

    = Table.SplitColumn(Source,"Column1",Splitter.SplitTextByDelimiter("#(lf)"))

    Regards,

    PQ


    Peter Q. http://blogs.msdn.com/peter_qian

    Monday, June 10, 2013 6:37 PM

All replies

  • This article might help you, although it will not specifically allow you to do want you want.

    http://blog.contextures.com/archives/2013/05/28/find-and-replace-line-breaks-in-excel/


    Unrecognized Excel MVP (UEM)

    Monday, June 10, 2013 3:56 PM
  • Thanks for the reply, but like you said it is not what I want...

    The source file will be updated every day and I don't want to make this change in Excel before using data explorer and the model built on it...

    The topics is still unanswered :)

    Monday, June 10, 2013 5:37 PM
  • Try

    = Table.SplitColumn(Source,"Column1",Splitter.SplitTextByDelimiter("#(cr)"))

    or

    = Table.SplitColumn(Source,"Column1",Splitter.SplitTextByDelimiter("#(lf)"))

    Regards,

    PQ


    Peter Q. http://blogs.msdn.com/peter_qian

    Monday, June 10, 2013 6:37 PM
  • Perfect !

    The formula "= Table.SplitColumn(Source,"Column1",Splitter.SplitTextByDelimiter("#(lf)"))" works very fine !

    Thanks !

    Simon P.

    Monday, June 10, 2013 7:03 PM