Reading Text into Excel

Hi, I have a couple of hundred of pages of data in a similar format, and wanted to see if there was some way in which I could input it into excel into columns as needed without having to make changes manually. The data is formatted as follows:

...
TA454516 UNIBANCO-UNIAO DE BANCOS BRAS. S.A. SP
UNIBANCO - UNIAO DE CAYMAN, ILHAS USD 140.663,25
TA454753 BANCO VOTORANTIM S.A. SP
ZURCHER KANTONALBAN SUICA CHF 1.710.000,00
TA454983 BANCO SANTANDER S.A. SP
TORONTO DOMINION IN CANADA USD 269.109,82
...

Where each two lines correspond to one row entry; the first element is a serial number (TA454516 ), followed by a name (UNIBANCO-UNIAO DE BANCOS BRAS. S.A.), then a desingation of two characters in the end of the first line (SP). the second line has a name (UNIBANCO - UNIAO DE CAYMAN, ILHAS ), followed by a currency designation (USD), and then a number (140.663,25). Can I somehow do it with a macro? With the data as is, there is one space between every word. Thanks so much for your help.

Thanks and regards,
fergzzq

Do you want the two lines to be in one row after you're done or still in two rows?

Assumptions:
No header row
Data is in column A
You don't want column A or gaps in rows after the splitting of the data.


Remove the ' in front of the blue '*1 if you want the data in the column to be usable as numeric value - I'm guessing this is "European" formatting of numbers.

Sub test()
Application.ScreenUpdating = False
Dim str1 As String, str2 As String, str3 As String, str4 As String
Dim lastRow As Long, i As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To lastRow
    If i Mod 2 = 1 Then
        Cells(i, 2) = Left(Cells(i, 1), InStr(Cells(i, 1), " ") - 1)
        Cells(i, 3) = Mid(Cells(i, 1), InStr(Cells(i, 1), " ") + 1, InStrRev(Cells(i, 1), " ") _
        - InStr(Cells(i, 1), " ") - 1)
    Else
        Cells(i - 1, 4) = Left(Cells(i, 1), InStrRev(Cells(i, 1), " ") - 5)
        Cells(i - 1, 5) = Mid(Cells(i, 1), InStrRev(Cells(i, 1), " ") - 3, 3)
        Cells(i - 1, 6) = Right(Cells(i, 1), Len(Cells(i, 1)) - InStrRev(Cells(i, 1), " ")) '*1
    End If
Next
'Exit Sub

'delete origal A column
Cells(1, 1).EntireColumn.Delete
'eliminates blank rows
For i = lastRow To 1 Step -1
    If Cells(i, 1) = "" Then Cells(i, 1).EntireRow.Delete
Next
    Range(Cells(1, 1), Cells(1, 5)).EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub

Perfect! Thank you so much I really appreciate the help!