Convert Text to Cell

[PaddyB]

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?

[#2]

@PaddyB 

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.

[#3]

@Hans Vogelaar 

strange... ok, i attached a 3 line CSV

[#4]

@PaddyB 

This is what I see when I open the .csv file in Excel:

And in Notepad++:

[#5]

@PaddyB 

Power Query could work if you consider such option.

[#6]

@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.

[#7]

@Riny_van_Eekelen 

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

[#8]

i think PQ is a idea, but to much - but i will check your solution. but "new in PQ"

[#9]

@PaddyB 

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.

[#10]

@Sergei Baklan 

hi

thank you Guys so much, for all you help.

i upload a manually edited version.

• column A to G is how i get i from my CSV import
• i then manually add for testing the column H, I, J 
• after it  i copied for testing the stuff by hand from column E to H, I, J

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

[#11 · Accepted solution]

@PaddyB 

Please checked attached in next sheet. I queried sample table in first sheet without manually added columns.

[#12]

Holy Cow :)

thanks for it... was now clicking in PowerQuery - as this tool is compl. new for me.
i think with this PQ i can now solve my Problem

great

[#13]

@Sergei Baklan 

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

[#14]

@PaddyB 

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

[#15]

@Sergei Baklan 

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"