Home

.xml file splitting lines into different rows

%3CLINGO-SUB%20id%3D%22lingo-sub-542098%22%20slang%3D%22en-US%22%3E.xml%20file%20splitting%20lines%20into%20different%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-542098%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20with%20an%20.xml%20file%20from%20a%20subtitling%20software%20and%20when%20I%20open%20it%20in%20excel%2C%20it%20creates%20a%20schema%20for%20it%20and%20then%20in%20the%20column%20with%20the%20subtitles%2C%20some%20of%20the%20subtitles%20are%20split%20into%20different%20rows%2C%20rather%20than%20being%20in%20one%20cell.%20There%20is%20another%20column%20which%20marks%20the%20line%20or%20subtitle%20number%2C%20so%20they%20are%20all%20marked%20with%20the%20same%20number%20when%20they%20come%20from%20the%20same%20subtitle%20but%20are%20split%20across%20two%20or%20three%20rows.%20I%20don't%20want%20to%20have%20to%20manually%20join%20all%20these%20rows%2C%20so%20I%20am%20wondering%20if%20there%20is%20some%20option%20or%20formula%20that%20would%20help%20me%20out%20with%20this%3F%3F%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-542098%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-542539%22%20slang%3D%22en-US%22%3ERe%3A%20.xml%20file%20splitting%20lines%20into%20different%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-542539%22%20slang%3D%22en-US%22%3E%3CP%3ECould%20you%20use%20'CONCAT'%20to%20get%20them%20all%20in%20one%20cell%3F%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F337083%22%20target%3D%22_blank%22%3E%40laura_y%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-542681%22%20slang%3D%22en-US%22%3ERe%3A%20.xml%20file%20splitting%20lines%20into%20different%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-542681%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F337087%22%20target%3D%22_blank%22%3E%40HuskerKnight%3C%2FA%3E%26nbsp%3BIf%20I%20have%20understood%20the%20CONCAT%20tool%20correctly%2C%20I%20think%20I%20would%20still%20need%20to%20manually%20check%20all%20the%20rows%20that%20need%20joining%20or%20not%2C%20as%20some%20do%20and%20some%20don't%2C%20so%20I%20am%20trying%20to%20find%20a%20fix%20which%20doesn't%20involve%20manually%20checking%20the%20whole%20file%E2%80%93%20unless%20I%20am%20missing%20something%20with%20how%20I%20can%20use%20the%20CONCAT%20tool%2C%20please%20let%20me%20know%20if%20so!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-549869%22%20slang%3D%22en-US%22%3ERe%3A%20.xml%20file%20splitting%20lines%20into%20different%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-549869%22%20slang%3D%22en-US%22%3E%3CP%3EDo%20you%20have%20an%20example%3F%26nbsp%3B%20I'm%20not%20sure%20I%20follow..%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F337083%22%20target%3D%22_blank%22%3E%40laura_y%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
laura_y
New Contributor

I am working with an .xml file from a subtitling software and when I open it in excel, it creates a schema for it and then in the column with the subtitles, some of the subtitles are split into different rows, rather than being in one cell. There is another column which marks the line or subtitle number, so they are all marked with the same number when they come from the same subtitle but are split across two or three rows. I don't want to have to manually join all these rows, so I am wondering if there is some option or formula that would help me out with this???


3 Replies

Could you use 'CONCAT' to get them all in one cell? @laura_y

@HuskerKnight If I have understood the CONCAT tool correctly, I think I would still need to manually check all the rows that need joining or not, as some do and some don't, so I am trying to find a fix which doesn't involve manually checking the whole file– unless I am missing something with how I can use the CONCAT tool, please let me know if so!

Do you have an example?  I'm not sure I follow..@laura_y 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies