Home

Splitting a row with multiple lines of text to different rows

%3CLINGO-SUB%20id%3D%22lingo-sub-1020315%22%20slang%3D%22en-US%22%3ESplitting%20a%20row%20with%20multiple%20lines%20of%20text%20to%20different%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1020315%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20excel%20sheet%20in%20which%20some%20rows%20have%20multiple%20lines%20of%20text%20and%20some%20have%20only%20one%20line.%3C%2FP%3E%3CP%3EI%20want%20to%20convert%20all%20those%20rows%20which%20have%20multiple%20lines%20to%20separate%20rows.%3C%2FP%3E%3CP%3ESee%20example%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOriginal%20table%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EOrder%3C%2FTD%3E%3CTD%3ELine%3C%2FTD%3E%3CTD%3EDate%3C%2FTD%3E%3CTD%3ESupplier%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3CBR%20%2F%3E2%3C%2FTD%3E%3CTD%3E1%3CBR%20%2F%3E2%3C%2FTD%3E%3CTD%3E1%2F12%2F2020%3CBR%20%2F%3E1%2F25%2F2020%3C%2FTD%3E%3CTD%3ESupplier1%3CBR%20%2F%3ESupplier2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E11%2F17%2F2019%3C%2FTD%3E%3CTD%3ESupplier3%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EConverted%20table%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EOrder%3C%2FTD%3E%3CTD%3ELine%3C%2FTD%3E%3CTD%3EDate%3C%2FTD%3E%3CTD%3ESupplier%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%2F12%2F2020%3C%2FTD%3E%3CTD%3ESupplier1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E1%2F25%2F2020%3C%2FTD%3E%3CTD%3ESupplier2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E11%2F17%2F2019%3C%2FTD%3E%3CTD%3ESupplier3%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20on%20how%20to%20achieve%20that%3F%20preferable%20without%20a%20macro.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3ELiron.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1020315%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1033011%22%20slang%3D%22en-US%22%3ERe%3A%20Splitting%20a%20row%20with%20multiple%20lines%20of%20text%20to%20different%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1033011%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F463168%22%20target%3D%22_blank%22%3E%40Eizenman%3C%2FA%3E%20I'm%20guessing%20the%20data%20is%20pasted%20into%20Excel%20from%20elsewhere%2C%20perhaps%20it%20is%20easier%20to%20try%20a%20different%20way%20to%20get%20that%20data%20into%20Excel%3F%20What%20is%20the%20source%20of%20this%20table%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1033017%22%20slang%3D%22en-US%22%3ERe%3A%20Splitting%20a%20row%20with%20multiple%20lines%20of%20text%20to%20different%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1033017%22%20slang%3D%22en-US%22%3E%3CP%3EI%20don't%20have%20a%20way%20to%20change%20how%20I%20get%20the%20data%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1033070%22%20slang%3D%22en-US%22%3ERe%3A%20Splitting%20a%20row%20with%20multiple%20lines%20of%20text%20to%20different%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1033070%22%20slang%3D%22en-US%22%3EThe%20question%20wasnt%20clear%20enough.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1033127%22%20slang%3D%22en-US%22%3ERe%3A%20Splitting%20a%20row%20with%20multiple%20lines%20of%20text%20to%20different%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1033127%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F463168%22%20target%3D%22_blank%22%3E%40Eizenman%3C%2FA%3E%20I'm%20not%20sure%20why%20you%20don't%20want%20to%20use%20VBA%2C%20but%20I%20couldn't%20come%20up%20with%20an%20easy%20alternative.%20The%20code%20below%20assumes%20all%20cells%20on%20a%20row%20contain%20the%20same%20number%20of%20%22lines%22%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EOption%20Explicit%0A%0ASub%20SplitTableROws()%0A%20%20%20%20Dim%20Data%20As%20Variant%0A%20%20%20%20Dim%20NewData%20As%20Variant%0A%20%20%20%20Dim%20SplitData%20As%20Variant%0A%20%20%20%20Dim%20DataRow%20As%20Long%0A%20%20%20%20Dim%20NewDataRow%20As%20Long%0A%20%20%20%20Dim%20Col%20As%20Long%0A%20%20%20%20Dim%20Line%20As%20Long%0A%0A%20%20%20%20Data%20%3D%20ActiveSheet.Range(%22A1%22).CurrentRegion.Value%0A%20%20%20%20NewData%20%3D%20Application.Transpose(Data)%0A%20%20%20%20For%20DataRow%20%3D%20LBound(Data%2C%201)%20To%20UBound(Data%2C%201)%0A%20%20%20%20%20%20%20%20NewDataRow%20%3D%20NewDataRow%20%2B%201%0A%20%20%20%20%20%20%20%20If%20InStr(Data(DataRow%2C%201)%2C%20Chr(10))%20%26gt%3B%200%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20ReDim%20Preserve%20NewData(1%20To%20UBound(Data%2C%202)%2C%201%20To%20NewDataRow%20%2B%20UBound(Split(Data(DataRow%2C%201)%2C%20Chr(10))))%0A%20%20%20%20%20%20%20%20%20%20%20%20For%20Col%20%3D%20LBound(Data%2C%202)%20To%20UBound(Data%2C%202)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20SplitData%20%3D%20Split(Data(DataRow%2C%20Col)%2C%20Chr(10))%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20For%20Line%20%3D%20LBound(SplitData)%20To%20UBound(SplitData)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20NewData(Col%2C%20NewDataRow%20%2B%20Line)%20%3D%20SplitData(Line)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Next%0A%20%20%20%20%20%20%20%20%20%20%20%20Next%0A%20%20%20%20%20%20%20%20%20%20%20%20NewDataRow%20%3D%20NewDataRow%20%2B%20Line%20-%201%0A%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20ReDim%20Preserve%20NewData(1%20To%20UBound(Data%2C%202)%2C%201%20To%20NewDataRow)%0A%20%20%20%20%20%20%20%20%20%20%20%20For%20Col%20%3D%20LBound(Data%2C%202)%20To%20UBound(Data%2C%202)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20NewData(Col%2C%20NewDataRow)%20%3D%20Data(DataRow%2C%20Col)%0A%20%20%20%20%20%20%20%20%20%20%20%20Next%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20DataRow%0A%20%20%20%20Range(%22F1%22).Resize(NewDataRow%2C%20UBound(Data%2C%202)).Value%20%3D%20Application.Transpose(NewData)%0AEnd%20Sub%0A%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1033233%22%20slang%3D%22en-US%22%3ERe%3A%20Splitting%20a%20row%20with%20multiple%20lines%20of%20text%20to%20different%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1033233%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%20I%20ran%20it%20but%20it%20just%20creates%20duplicated%20the%20column%20headers%20to%20the%20right%20of%20the%20existing%20columns%20without%20splitting%20the%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAm%20I%20supposed%20to%20take%20additional%20steps%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Eizenman
New Contributor

Hello all,

 

I have an excel sheet in which some rows have multiple lines of text and some have only one line.

I want to convert all those rows which have multiple lines to separate rows.

See example below.

 

Original table:

OrderLineDateSupplier
1
2
1
2
1/12/2020
1/25/2020
Supplier1
Supplier2
3311/17/2019Supplier3

 

Converted table:

OrderLineDateSupplier
111/12/2020Supplier1
221/25/2020Supplier2
3311/17/2019Supplier3

 

Any ideas on how to achieve that? preferable without a macro.

 

Thanks,

Liron.

7 Replies

@Eizenman I'm guessing the data is pasted into Excel from elsewhere, perhaps it is easier to try a different way to get that data into Excel? What is the source of this table?

I don't have a way to change how I get the data

The question wasnt clear enough.

@Eizenman I'm not sure why you don't want to use VBA, but I couldn't come up with an easy alternative. The code below assumes all cells on a row contain the same number of "lines":

Option Explicit

Sub SplitTableROws()
    Dim Data As Variant
    Dim NewData As Variant
    Dim SplitData As Variant
    Dim DataRow As Long
    Dim NewDataRow As Long
    Dim Col As Long
    Dim Line As Long

    Data = ActiveSheet.Range("A1").CurrentRegion.Value
    NewData = Application.Transpose(Data)
    For DataRow = LBound(Data, 1) To UBound(Data, 1)
        NewDataRow = NewDataRow + 1
        If InStr(Data(DataRow, 1), Chr(10)) > 0 Then
            ReDim Preserve NewData(1 To UBound(Data, 2), 1 To NewDataRow + UBound(Split(Data(DataRow, 1), Chr(10))))
            For Col = LBound(Data, 2) To UBound(Data, 2)
                SplitData = Split(Data(DataRow, Col), Chr(10))
                For Line = LBound(SplitData) To UBound(SplitData)
                    NewData(Col, NewDataRow + Line) = SplitData(Line)
                Next
            Next
            NewDataRow = NewDataRow + Line - 1
        Else
            ReDim Preserve NewData(1 To UBound(Data, 2), 1 To NewDataRow)
            For Col = LBound(Data, 2) To UBound(Data, 2)
                NewData(Col, NewDataRow) = Data(DataRow, Col)
            Next
        End If
    Next DataRow
    Range("F1").Resize(NewDataRow, UBound(Data, 2)).Value = Application.Transpose(NewData)
End Sub

@Jan Karel Pieterse  I ran it but it just creates duplicated the column headers to the right of the existing columns without splitting the rows.

 

Am I supposed to take additional steps?

No, but you must make sure there are no empty rows between row 1 and the remainder of the data. I took the sample data you posted and placed that little table in A1:D4

@Eizenman 

One more variant with Power Query. Not everything from user interface, the script is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnpivotColumns = Table.UnpivotOtherColumns(
        Source,
        {},
        "Attribute", "Value"
    ),
    DateToText = Table.ReplaceValue(
        UnpivotColumns,
        each [Value],
        each
            if Type.Is(Value.Type([Value]),DateTime.Type)
            then Date.ToText(DateTime.Date([Value]),"yyyy-MM-dd")
            else [Value],
        Replacer.ReplaceValue,{"Value"}
    ),
    AllToText = Table.TransformColumnTypes(
        DateToText,
        {{"Value", type text}}
    ),
    TransformColumn = Table.TransformColumns(
        AllToText,
        {
            {"Value", Splitter.SplitTextByDelimiter("#(lf)")}
        }
    ),
    ExpandToRows = Table.ExpandListColumn(
        TransformColumn,
        "Value"
    ),
    GroupRows = Table.Group(
        ExpandToRows,
        {"Attribute"},
        {{"Group", each [Value]}}
    ),
    Result = Table.FromRows(
        List.Zip(GroupRows[Group]),
        GroupRows[Attribute]
    ),
    ProperType = Table.TransformColumnTypes(
        Result,
        {
            {"Line", Int64.Type},
            {"Date", type date},
            {"Supplier", type text}
        }
    )
in
    ProperType

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Discussion - Updating our interface with Fluent touches
Elliot Kirk in Discussions on
102 Replies