Post horizontally Notepad to Excel

[edfclaya]

hello!
requesting for help in pasting data horizontally from Notepad to Excel.
Delimiter would be equals from the notepad.


Sample notepad details:
============
Date period
Company Details
Company email address
contact number
============
Date period
Company Details
Company email address
contact number

 

[HighAndWilder]

hello!
requesting for help in pasting data horizontally from Notepad to Excel.
Delimiter would be equals from the notepad.


Sample notepad details:
============
Date period
Company Details
Company email address
contact number
============
Date period
Company Details
Company email address
contact number

Try this code.

Change the destination worksheet name where indicated.

Run the first procedure.

I am assuming that the text file is consistently formatted.

It will prompt you to select the text file.

Public Sub subReadTextFile()
Dim strFile As String, strLine As String
Dim intRow As Integer
Dim intCol As Integer
Dim Ws As Worksheet

  ActiveWorkbook.Save
  
  Set Ws = Worksheets("Target") ' <<< Change the destination worksheet here. >>>
  
  Ws.Activate
  
  Ws.Cells.Clear
  
  intRow = 1
  
  intCol = 1
  
  Ws.Range("A1:D1").Value = Array("Date Period", "Company Details", "Company Email Address", "Contact Number")
  
  strFile = fncSelectFileDialog("Select Text File.")
  
  If strFile = "" Then
    Exit Sub
  End If
   
  Open strFile For Input As #1
   
  Do Until EOF(1)
    
    Line Input #1, strLine
    
    strLine = Trim(strLine)
    
    If Len(Trim(strLine)) > 0 Then
    
      If strLine = "============" Then
        intRow = intRow + 1
        intCol = 1
      Else
        Ws.Cells(intRow, intCol).Value = strLine
        intCol = intCol + 1
      End If
    
    End If
    
  Loop
   
  Close #1
  
  Ws.Cells.EntireColumn.AutoFit
    
  MsgBox intRow - 1 & " rows imported.", vbOKOnly, "Confirmation"
   
End Sub

Public Function fncSelectFileDialog(strTitle As String) As String
Dim strFile As Variant

  strFile = Application.GetOpenFilename(FileFilter:="Text Files (*.txt*),*txt*", Title:=strTitle)
  
  If strFile = False Then
    fncSelectFileDialog = ""
    Exit Function
  End If
  
  fncSelectFileDialog = strFile
  
End Function

 

[alansidman]

Another Option with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([#"Sample notepad details:"] <> "============")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 4), type number),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-US")[Modulo]), "Modulo", "Sample notepad details:"),
    #"Filled Up" = Table.FillUp(#"Pivoted Column",{"0", "3", "2"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Up", each ([1] = "Date period")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Index"})
in
    #"Removed Columns"