Aug 13 2022 02:59 AM - edited Aug 13 2022 03:00 AM
Hi
i have in a cell a Multiple Line Text - what i want to split in columns
"Start Time (UTC): 8/8/2022 11:58:26 AM
End Time (UTC): 8/8/2022 12:49:08 PM
Duration: 00:50:42.5186092
[8/8/2022 11:58:26 AM (UTC)] xxx@xxx joined.
[8/8/2022 12:26:00 PM (UTC)] xxx@xxx left.
[8/8/2022 11:59:41 AM (UTC)] xxx@xxx joined.
[8/8/2022 12:49:08 PM (UTC)] xxx@xxx left."
i test it with "Text to column" function and as a delimiter "CTRL + J" - it takes the first line, that's it.
for me, the solution would be if i just can Splitt the first 3 lines to separate columns
"Start Time (UTC): 8/8/2022 11:58:26 AM
End Time (UTC): 8/8/2022 12:49:08 PM
Duration: 00:50:42.5186092
any idea for a absolutely NO Excel Guy?
Aug 13 2022 03:57 AM
This is what I get if I copy your example into a cell and apply Text to Columns with Ctrl+J as delimiter:
Perhaps your data actually have a different delimiter?
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.
Aug 13 2022 04:26 AM
@Hans Vogelaar
strange... ok, i attached a 3 line CSV
Aug 13 2022 05:46 AM
Aug 13 2022 05:53 AM
Aug 13 2022 06:15 AM
@PaddyB The PQ solution provided by @Sergei Baklan made me realize that you perhaps wanted a different solution. One that I had otherwise not thought of.
See attached.
Aug 13 2022 06:32 AM
thanks for the Idea. this split is more than i need :)
would be happy just have the 3 "1st Lines" in a separate cell
for example
Starte Time line in one cell
End Time Line in one cell
Duartion Time in one Cell
end the rest if possible in a last cell
Aug 13 2022 06:33 AM
Aug 13 2022 07:16 AM
Yes, that's just an idea. If you share in which exactly form and format you'd like to have the result it'll be easy with concrete suggestion.
Aug 13 2022 10:08 AM
hi
thank you Guys so much, for all you help.
i upload a manually edited version.
Line 2 and 3 are a result what would makes me already happy :)
Line 4 and 5 are the ultimate "i can go to weekend and relax" version
Aug 13 2022 01:42 PM
SolutionPlease checked attached in next sheet. I queried sample table in first sheet without manually added columns.
Aug 13 2022 02:44 PM
Aug 13 2022 04:05 PM
was running the Query on 1 of my files with 5k entry's - very nice
but i think there is still a small problem.or maybe 2 :)
the source files have UTC time-zone, i think in this way also the US-Dateforma. but witht eh query we just "format" this new
see PrintScreen. so, it should be the 4. August 2022 and with he Query we make out if this 8. April
or I'm wrong?
i also have approx 529 errors... maybe connected to the same problem above
Aug 14 2022 09:06 AM
I see, datetime is in US locale, with sample 8/8/2022 it was hard to understand. To convert from specific locale select column(s), right click, Change type->Using locale
and use here English US
or simply add culture in formula bar
Aug 14 2022 12:17 PM
very interesting what PQ can do - thank you for showing me
i had to do some changes, because i got after approx. 500 some Erros - something about date
not sure if my changes make any sense :) but the error is gone
let
Source = Excel.CurrentWorkbook(){[Name="TeamsMeetings"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(Source,{"Received UTC", "Sender", "ToRecipient", "ccRecipient", "Subject", "Importance", "Body"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Text.Split([Body], "#(lf)" )),
AddStartTime = Table.AddColumn(#"Added Custom", "Start Time (UTC)", each [Custom]{0}),
AddEndTime = Table.AddColumn(AddStartTime, "End Time (UTC)", each [Custom]{1}),
AddDuration = Table.AddColumn(AddEndTime, "Duration", each [Custom]{2}),
#"Removed Columns" = Table.RemoveColumns(AddDuration,{"Custom"}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Removed Columns", {{"Start Time (UTC)", each Text.AfterDelimiter(_, "Start Time (UTC): "), type text}}),
#"Extracted Text After Delimiter1" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"End Time (UTC)", each Text.AfterDelimiter(_, "End Time (UTC): "), type text}}),
#"Extracted Text After Delimiter2" = Table.TransformColumns(#"Extracted Text After Delimiter1", {{"Duration", each Text.AfterDelimiter(_, "Duration: "), type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Text After Delimiter2",{{"End Time (UTC)", type datetime}, {"Start Time (UTC)", type datetime}, {"Duration", type datetime}, {"Received UTC", type datetime}}),
#"Geänderter Typ mit Gebietsschema" = Table.TransformColumnTypes(#"Extracted Text After Delimiter2", {{"Start Time (UTC)", type text}}, "en-US"),
#"Geänderter Typ mit Gebietsschema1" = Table.TransformColumnTypes(#"Geänderter Typ mit Gebietsschema", {{"End Time (UTC)", type text}}, "en-US"),
#"Geänderter Typ mit Gebietsschema2" = Table.TransformColumnTypes(#"Geänderter Typ mit Gebietsschema", {{"Received UTC", type number}}, "en-US")
in
#"Geänderter Typ mit Gebietsschema1"
Aug 13 2022 01:42 PM
SolutionPlease checked attached in next sheet. I queried sample table in first sheet without manually added columns.