Cleaning all text between two characters or extracting specific data from a cell

[abhishukla15]

Hi,

I have incident log in a cell and wanted to extract the date/time and user details from the cell and remove other details.
Example : I have incident log as " 30/03/19 18:00:05 Europe/ (shuklla) : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 29/03/2019 20:35:09 Europe/ - (ASTU REK Tec) : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 28/03/19 08:40:29 Europe/ - (0002345) : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
I'm not sure about no. of "x" character between two dates. Now I want my output as below in a corresponding cell :

30/03/19 18:00:05 Europe/ (shuklla)
29/03/19 20:35:09 Europe/ - (ASTU REK Tec)
28/03/19 08:40:29 Europe/ - (0002345)

Thanks in advance for help

 

[DanteAmor]

Try this

<b></b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:398px;" /><col style="width:361px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:211px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:left; "> 30/03/19 18:00:05 Europe/ (shuklla) : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxx 29/03/2019 20:35:09 Europe/ - (ASTU REK Tec) : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 28/03/19 08:40:29 Europe/ - (0002345) : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx</td><td style="text-align:left; ">30/03/19 18:00:05 Europe/ (shuklla) <br />29/03/2019 20:35:09 Europe/ - (ASTU REK Tec)<br />28/03/19 08:40:29 Europe/ - (0002345)</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=MID(A2,SEARCH("/",A2)-2,SEARCH(") :",A2))&CHAR(10)&TRIM(LEFT(SUBSTITUTE(MID(A2,SEARCH("/",A2,SEARCH(") :",A2))-2,1000),") :",")" & REPT(" ",250)),200))&CHAR(10)&TRIM(LEFT(SUBSTITUTE(MID(A2,SEARCH("/",A2,SEARCH("#$#$",SUBSTITUTE(A2,") :","#$#$",2)))-2,1000),") :",")" & REPT(" ",250)),200))</td></tr></table></td></tr></table> <br /><br />

 

[DanteAmor]

Another alternative



<td style="text-align:left; ">30/03/19 18:00:05 Europe/ (shuklla) <br />29/03/2019 20:35:09 Europe/ - (ASTU REK Tec)<br />28/03/19 08:40:29 Europe/ - (0002345)</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >cell</td><td >Formula</td></tr><tr><td >B2</td><td >=MID(A2,SEARCH("/",A2)-2,SEARCH(") :",A2))&CHAR(10)&TRIM(LEFT(SUBSTITUTE(REPLACE(A2,1,SEARCH("#$#",SUBSTITUTE(A2,"/","#$#",4))-3,""),") :",")"&REPT(" ",250)),200))&CHAR(10)&TRIM(LEFT(SUBSTITUTE(REPLACE(A2,1,SEARCH("#$#",SUBSTITUTE(A2,"/","#$#",7))-3,""),") :",")"&REPT(" ",250)),200))</td></tr></table></td></tr></table> <br /><br />

 

[sandy666]

PowerQuery (Get&Transform) alternative

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]30/03/19 18:00:05 Europe/ (shuklla) : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxx 29/03/2019 20:35:09 Europe/ - (ASTU REK Tec) : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 28/03/19 08:40:29 Europe/ - (0002345) : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx[/td][td][/td][td=bgcolor:#E2EFDA]30/03/19 18:00:05 Europe/ (shuklla)[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]29/03/2019 20:35:09 Europe/ - (ASTU REK Tec)[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]28/03/19 08:40:29 Europe/ - (0002345) :[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Replace = Table.ReplaceValue(Source,"x","",Replacer.ReplaceText,{"Column1"}),
    TrimB = Table.TransformColumns(Replace,{{"Column1", Text.Trim, type text}}),
    Split = Table.ExpandListColumn(Table.TransformColumns(TrimB, {{"Column1", Splitter.SplitTextByDelimiter(": ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    TrimA = Table.TransformColumns(Split,{{"Column1", Text.Trim, type text}})
in
    TrimA[/SIZE]