Extract words from cells

[kudanrid]

Hello,

This is my first post here.

How do I extract words from this cell ...

<tbody>
[TD="class: xl67"]03.07.2019 14:57:57 - Jasmine Chew (Work notes)
machine offline

02.07.2019 14:02:23 - Eddie Sayari (Work notes)
Nuance aG - Nuance - Nuance Power PDF Advanced 3.0 - WIN EN - SR05271 - 1.0 3.0
Running application deployment evaluation cycle...

23.06.2019 21:29:22 - Eddie Sayari (Work notes)
Nuance aG - Nuance - Nuance Power PDF Advanced 3.0 - WIN EN - SR05271 - 1.0 3.0
Status unknown - This computer might be offline

20.06.2019 17:09:43 - Balazs Kis (Work notes)
new sccm

Nuance aG - Nuance - Nuance Power PDF Advanced 3.0 - WIN EN - SR05271 - 1.0 3.0
Adding to install collection...[/TD]

</tbody>

So I'll end up with just date/time and its corresponding name like below

<tbody>
[TD="class: xl66"]03.07.2019 14:57:57 - Jasmine Chew
02.07.2019 14:02:23 - Eddie Sayari
23.06.2019 21:29:22 - Eddie Sayari
20.06.2019 17:09:43 - Balazs Kis[/TD]

</tbody>

Any help is much appreciated

 

[mole999]

are the all the same format ?

 

[Fluff]

Assuming the text is in separate cells, try

Book1
	A	B
1	03.07.2019 14:57:57 - Jasmine Chew (Work notes) machine offline	03.07.2019 14:57:57 - Jasmine Chew
2	02.07.2019 14:02:23 - Eddie Sayari (Work notes) Nuance aG - Nuance - Nuance Power PDF Advanced 3.0 - WIN EN - SR05271 - 1.0 3.0 Running application deployment evaluation cycle	02.07.2019 14:02:23 - Eddie Sayari
3	23.06.2019 21:29:22 - Eddie Sayari (Work notes) Nuance aG - Nuance - Nuance Power PDF Advanced 3.0 - WIN EN - SR05271 - 1.0 3.0 Status unknown - This computer might be offline	23.06.2019 21:29:22 - Eddie Sayari
4	20.06.2019 17:09:43 - Balazs Kis (Work notes) new sccm Nuance aG - Nuance - Nuance Power PDF Advanced 3.0 - WIN EN - SR05271 - 1.0 3.0 Adding to install collection	20.06.2019 17:09:43 - Balazs Kis
Sheet1

Cell Formulas
Range	Formula
B1	=IFERROR(LEFT(A1,FIND("(",A1)-1),"")

 

[kudanrid]

no they're not in separate cell, all that are in a cell. There will be many more cell like it and their character length are unlikely to be the same.

 

[Rick Rothstein]

no they're not in separate cell, all that are in a cell.

Here is a UDF (user defined function) that you can try...

[table="width: 500"]
[tr]
	[td]Function DateName(S As String) As String
  Dim X As Long, vNum As Variant, Arr As Variant
  Arr = Split(S, vbLf & vbLf)
  For X = 0 To UBound(Arr)
    If Arr(X) Like "##.##.####*" Then
      Arr(X) = Trim(Left(Arr(X), InStr(Arr(X), "(") - 1))
    Else
      Arr(X) = ""
    End If
  Next
  DateName = Join(Arr, vbLf)
  For Each vNum In Array(121, 13, 5, 3, 3, 2)
    DateName = Replace(DateName, String(vNum, vbLf), vbLf)
  Next
  If Right(DateName, 1) = vbLf Then DateName = Left(DateName, Len(DateName) - 1)
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use DateName just like it was a built-in Excel function. For example,

=DateName(A1)

NOTE: Make sure to turn Wrap Text on for the cell you put this formula in.

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Peter_SSs]

Welcome to the MrExcel board!

You could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down. You may need to format the result column with 'Wrap Text'.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function GetEm(s As String) As String
  Dim RX As Object, m As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(\d\d\.){2}\d{4} (\d\d:){2}\d{2} - .+?(?= ?\()"
  For Each m In RX.Execute(s)
    GetEm = GetEm & Chr(10) & m
  Next m
  GetEm = Mid(GetEm, 2)
End Function

Excel Workbook
	A	B
1	03.07.2019 14:57:57 - Jasmine Chew (Work notes)machine offline02.07.2019 14:02:23 - Eddie Sayari (Work notes)Nuance aG - Nuance - Nuance Power PDF Advanced 3.0 - WIN EN - SR05271 - 1.0 3.0 Running application deployment evaluation cycle...23.06.2019 21:29:22 - Eddie Sayari (Work notes)Nuance aG - Nuance - Nuance Power PDF Advanced 3.0 - WIN EN - SR05271 - 1.0 3.0 Status unknown - This computer might be offline20.06.2019 17:09:43 - Balazs Kis (Work notes)new sccmNuance aG - Nuance - Nuance Power PDF Advanced 3.0 - WIN EN - SR05271 - 1.0 3.0 Adding to install collection...	03.07.2019 14:57:57 - Jasmine Chew02.07.2019 14:02:23 - Eddie Sayari23.06.2019 21:29:22 - Eddie Sayari20.06.2019 17:09:43 - Balazs Kis
Sheet1

 

[kudanrid]

Thanks to both of you! it works i'm so glad i came here. i can go to sleep now

 

[sandy666]

with PowerQuery

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]03.07.2019 14:57:57 - Jasmine Chew (Work notes)
machine offline

02.07.2019 14:02:23 - Eddie Sayari (Work notes)
Nuance aG - Nuance - Nuance Power PDF Advanced 3.0 - WIN EN - SR05271 - 1.0 3.0
Running application deployment evaluation cycle...

23.06.2019 21:29:22 - Eddie Sayari (Work notes)
Nuance aG - Nuance - Nuance Power PDF Advanced 3.0 - WIN EN - SR05271 - 1.0 3.0
Status unknown - This computer might be offline

20.06.2019 17:09:43 - Balazs Kis (Work notes)
new sccm

Nuance aG - Nuance - Nuance Power PDF Advanced 3.0 - WIN EN - SR05271 - 1.0 3.0
Adding to install collection...[/td][td][/td][td=bgcolor:#E2EFDA]03.07.2019 14:57:57 - Jasmine Chew
02.07.2019 14:02:23 - Eddie Sayari
23.06.2019 21:29:22 - Eddie Sayari
20.06.2019 17:09:43 - Balazs Kis[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    If = Table.AddColumn(Split, "Custom", each if Text.Contains([Column1], " (") then "x" else null),
    Filter = Table.SelectRows(If, each ([Custom] = "x")),
    ExtractBD = Table.TransformColumns(Filter, {{"Column1", each Text.BeforeDelimiter(_, " ("), type text}}),
    Group = Table.Group(ExtractBD, {"Custom"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom.1", each Table.Column([Count],"Column1")),
    Extract = Table.TransformColumns(List, {"Custom.1", each Text.Combine(List.Transform(_, Text.From), "#(lf)"), type text}),
    ROC = Table.SelectColumns(Extract,{"Custom.1"})
in
    ROC[/SIZE]

 

[Peter_SSs]

Thanks to both of you! it works i'm so glad i came here. i can go to sleep now

You're welcome.