1 text row to single column

[jinomar25]

I have problem in making this. format in a text file
Note: The text is only on A1, separated by a delimiter indicator which is "^"

https://docs.google.com/file/d/0B_eArBbGfxv4OTJ2YTlDaFBnZDA/edit?usp=sharing

To this . . . after text file multiple import to excel
As you can see in the image, all the text will be in one column which is A1.

https://docs.google.com/file/d/0B_eArBbGfxv4T3dpUnZaQlFVdVk/edit?usp=sharing

Here's the program I am working on:

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False


Dim oFileDialog As FileDialog
Dim LoopFolderPath As String
Dim oFileSystem As FileSystemObject
Dim oLoopFolder As Folder
Dim oFilePath As File
Dim oFile As TextStream
Dim RowN As Long
Dim ColN As Long
Dim iAnswer As Integer
On Error GoTo ERROR_HANDLER


Set oFileDialog = Application.FileDialog(msoFileDialogFolderPicker)


RowN = 1
ColN = 1


        With oFileDialog
        If .Show Then
    
    ActiveSheet.Columns(ColN).Cells.Clear
    LoopFolderPath = .SelectedItems(1) & "\"


    Set oFileSystem = CreateObject("Scripting.FileSystemObject")
    Set oLoopFolder = oFileSystem.GetFolder(LoopFolderPath)


    For Each oFilePath In oLoopFolder.Files
    Set oFile = oFileSystem.OpenTextFile(oFilePath)


       With oFile
    
    Do Until .AtEndOfStream
    
    ActiveSheet.Cells(RowN, ColN).Value = .ReadLine
    LoopFolderPath = Space(1)
    RowN = RowN + 1
     
        Loop
  
        .Close
    
    End With
      
      'oLoopFolderPath = Split(ActiveCell, "^")
      'ActiveCell.Resize(UBound(oLoopFolderPath) + 1) = WorksheetFunction.Transpose(oLoopFolderPath)
        ActiveSheet.Range("A:A").TextToColumns Destination:=Range("A1") _
        , DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Other:=True _
        , OtherChar:="^"
    
         ActiveSheet.UsedRange.Columns.AutoFit
         


    Next oFilePath
    
    End If
        
         iAnswer = MsgBox("Your Textfiles have been Inputted.", vbInformation)


    End With


EXIT_SUB:
Set oFilePath = Nothing
Set oLoopFolder = Nothing
Set oFileSystem = Nothing
Set oFileDialog = Nothing


    Application.ScreenUpdating = True


    Exit Sub


ERROR_HANDLER:
    Err.Clear
    GoTo EXIT_SUB


End Sub

Please Help. Thank you

 

[Rambo4711]

I'm not sure what you want to accomplish. As I understand your textfile contains text on one line separated by the delimiter indicator "^". Do you want the text parts to be presented on individual rows in column A?

 

[jinomar25]

the text having the delimiter will be split into one column, not many, just a single column "A"

 

[Rambo4711]

If you use Excel text import guide (open the text file within Excel) you can define the tab separator ^ as Other.
When Excel has converted the file you will have the text parts in different columns.
If you want the data to be presented in column A just mark all data - Copy - Goto cell A1 - Paste special values - Transposed