Converting one column of text into two columns.

[Eloise T]

When I copy the attached document file to Excel, it puts the data (text) on each line(row) in one cell (column A). In order to properly massage the data, I need a macro to "take the data in column A, one line(row) at a time, find the first space, leave the data (text) that is left of the space in column A and put the remaining data in column B. For example, if cell A1 contains "John Smith is an Excel programmer". Running the macro will leave "John" in column A and put "Smith is an Excel programmer" in column B....and continue on down the column, line-by-line until the data is exhausted. Thanks in advance!

 

[AlanSidman]

Eloise
I would do this with Power Query. Here is the Mcode to make that happen.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"})
in
    #"Split Column by Delimiter"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

 

[shrivallabha]

It can be done with formulas as well.

Below is basic example of code that should work, edit it to suit your needs.

Sub SplitDataInTwoColumns()
Dim wks As Worksheet
Set wks = ActiveSheet
For i = 1 To wks.Range("A" & wks.Rows.Count).End(xlUp).Row
    wks.Range("B" & i).Value = Left(wks.Range("A" & i).Value, InStr(1, wks.Range("A" & i).Value, " ", vbTextCompare) - 1)
    wks.Range("C" & i).Value = Mid(wks.Range("A" & i).Value, InStr(1, wks.Range("A" & i).Value, " ", vbTextCompare) + 1, Len(wks.Range("A" & i).Value))
Next i
End Sub

 

[Marc L]

A VBA demonstration how to directly import the text file - located in the workbook folder - and split its data,​

paste the code to the Sheet1 worksheet module :​

Sub Demo1()
    Dim V, R&, S$()
        V = ThisWorkbook.Path & "\sample data.txt":  If Dir(V) = "" Then Beep: Exit Sub
        Me.UsedRange.Clear
        R = FreeFile
        Open V For Input As #R
        S = Split(Input(LOF(R), #R), vbCrLf)
        Close #R
        ReDim V(UBound(S), 1)
    For R = 0 To UBound(S)
        V(R, 0) = Split(S(R))(0)
        V(R, 1) = Mid(S(R), Len(V(R, 0)) + 2)
    Next
        [A1:B1].Resize(R) = V
        Me.UsedRange.Columns.AutoFit
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Eloise T]

A VBA demonstration how to directly import the text file - located in the workbook folder - and split its data,​

paste the code to the Sheet1 worksheet module :​

Sub Demo1()
    Dim V, R&, S$()
        V = ThisWorkbook.Path & "\sample data.txt":  If Dir(V) = "" Then Beep: Exit Sub
        Me.UsedRange.Clear
        R = FreeFile
        Open V For Input As #R
        S = Split(Input(LOF(R), #R), vbCrLf)
        Close #R
        ReDim V(UBound(S), 1)
    For R = 0 To UBound(S)
        V(R, 0) = Split(S(R))(0)
        V(R, 1) = Mid(S(R), Len(V(R, 0)) + 2)
    Next
        [A1:B1].Resize(R) = V
        Me.UsedRange.Columns.AutoFit
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

Once I got the file name worked out for the data/text file, I ran the macro in Debug mode. It made it to the following two lines:

V(R, 0) = Split(S(R))(0)
V(R, 1) = Mid(S(R), Len(V(R, 0)) + 2)
Next

vacillated back and forth dozens of times and then ended with:


There were some blank lines 114 and 115 rows down. Would that cause the above error message?

 

[Eloise T]

It can be done with formulas as well.

Below is basic example of code that should work, edit it to suit your needs.

Sub SplitDataInTwoColumns()
Dim wks As Worksheet
Set wks = ActiveSheet
For i = 1 To wks.Range("A" & wks.Rows.Count).End(xlUp).Row
    wks.Range("B" & i).Value = Left(wks.Range("A" & i).Value, InStr(1, wks.Range("A" & i).Value, " ", vbTextCompare) - 1)
    wks.Range("C" & i).Value = Mid(wks.Range("A" & i).Value, InStr(1, wks.Range("A" & i).Value, " ", vbTextCompare) + 1, Len(wks.Range("A" & i).Value))
Next i
End Sub

How can this be modified to cause EOD [end of data] to be 10 blank lines/rows?
Thanks!

 

[Eloise T]

Eloise
I would do this with Power Query. Here is the Mcode to make that happen.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"})
in
    #"Split Column by Delimiter"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

I watched the link for an introduction to Power Query functionality. I never cease to be amazed with Excel features and functionality that I've not been previously been introduced. Pivot Tables is a good example. I'm not an Excel power user, and creating pivot tables in a spreadsheet can be a daunting task. I view Power Query the same way. Once mastered though, both appear incredibly valuable for anyone looking to quickly glean information from their data particularly in a repetitive manner.

 

[Marc L]

There were some blank lines 114 and 115 rows down.

As your lottery attachment contains only 4 rows - did you at least try with it ? No issue on my side ! -​

as again your attachment does not very reflect the real file …​

Anyway you could check if each element of the S array is not empty.​

​

Or just try this mod : S = Filter(Split(Input(LOF(R), #R), vbCrLf), "/", True)​

​

You may Like it !​

​

For more homework help just attach a relevant text file according to your issue …​

​

 

[AlanSidman]

Eloise,
If you want to expand your horizons with Power Query, pick up a copy of "M is for (Data) Monkey." It is a great primer in learning PQ. It is around $20 on Amazon.

 

[AlanSidman]

How can this be modified to cause EOD [end of data] to be 10 blank lines/rows?
Thanks!

I'm thinking that this would be added in native excel after completing your work in PQ and the data is transferred back to Native Excel.

 

[shrivallabha]

How can this be modified to cause EOD [end of data] to be 10 blank lines/rows?
Thanks!

Sorry, I do not understand your requirement. After finishing the data, do you want to keep 10 blank lines and then create an entry which says "End of Data"?

 

[Eloise T]

Sorry, I do not understand your requirement. After finishing the data, do you want to keep 10 blank lines and then create an entry which says "End of Data"?

Currently the macro stops as soon as it sees a blank line/row. Since there aren't any more than 3 blank lines in a row, I want to be able in Insert 10 blank lines to "tell" the macro to stop wherever I want it to stop. Does that make sense?

 

[shrivallabha]

Currently the macro stops as soon as it sees a blank line/row. Since there aren't any more than 3 blank lines in a row, I want to be able in Insert 10 blank lines to "tell" the macro to stop wherever I want it to stop. Does that make sense?

Posted macro will fail / stop if there's cell that doesn't contain space. This condition means that it will not work with blank cells either. So a test needs to be added to see if a cell contains a space. See below edited code.

Sub SplitDataInTwoColumns()
Dim wks As Worksheet
Set wks = ActiveSheet
For i = 1 To wks.Range("A" & wks.Rows.Count).End(xlUp).Row
    If InStr(1, wks.Range("A" & i).Value, " ", vbTextCompare) > 0 Then
        wks.Range("B" & i).Value = Left(wks.Range("A" & i).Value, InStr(1, wks.Range("A" & i).Value, " ", vbTextCompare) - 1)
        wks.Range("C" & i).Value = Mid(wks.Range("A" & i).Value, InStr(1, wks.Range("A" & i).Value, " ", vbTextCompare) + 1, Len(wks.Range("A" & i).Value))
    End If
Next i
End Sub

 

[Marc L]

So a test needs to be added to see if a cell contains a space.

Like my mod in post #8 …​

​

 

[Eloise T]

Posted macro will fail / stop if there's cell that doesn't contain space. This condition means that it will not work with blank cells either. So a test needs to be added to see if a cell contains a space. See below edited code.

Sub SplitDataInTwoColumns()
Dim wks As Worksheet
Set wks = ActiveSheet
For i = 1 To wks.Range("A" & wks.Rows.Count).End(xlUp).Row
    If InStr(1, wks.Range("A" & i).Value, " ", vbTextCompare) > 0 Then
        wks.Range("B" & i).Value = Left(wks.Range("A" & i).Value, InStr(1, wks.Range("A" & i).Value, " ", vbTextCompare) - 1)
        wks.Range("C" & i).Value = Mid(wks.Range("A" & i).Value, InStr(1, wks.Range("A" & i).Value, " ", vbTextCompare) + 1, Len(wks.Range("A" & i).Value))
    End If
Next i
End Sub

Fantastic! Works excellently! THANK YOU, SIR!
Can you explain how For i = 1 To wks.Range("A" & wks.Rows.Count).End(xlUp).Row is parsed?
...in other words For i = 1 To wks.Range is worksheet range. How is the limit determined in wks.Range?
What does "A" refer to?
How does concatenate wks.Rows.Count refer to?
and finally, how does End(xlUp).Row fit in the formula?

 

[Eloise T]

As your lottery attachment contains only 4 rows - did you at least try with it ? No issue on my side ! -​

as again your attachment does not very reflect the real file …​

Anyway you could check if each element of the S array is not empty.​

​

Or just try this mod : S = Filter(Split(Input(LOF(R), #R), vbCrLf), "/", True)​

​

You may Like it !​

​

For more homework help just attach a relevant text file according to your issue …​

​

I updated with the mod.
Here's a sample of real data..see attached:

 

[Marc L]

As there is no more date in your last attachment just replace in my mod the slash with a space​

or with a dot, depending on which lines you want to keep (via VBA Filter function) …​

​

 

[Eloise T]

As there is no more date in your last attachment just replace in my mod the slash with a space​

or with a dot, depending on which lines you want to keep (via VBA Filter function) …​

​

Thanks for your assistance. I'll try that.

 

[shrivallabha]

Fantastic! Works excellently! THANK YOU, SIR!
Can you explain how For i = 1 To wks.Range("A" & wks.Rows.Count).End(xlUp).Row is parsed?
...in other words For i = 1 To wks.Range is worksheet range. How is the limit determined in wks.Range?
What does "A" refer to?
How does concatenate wks.Rows.Count refer to?
and finally, how does End(xlUp).Row fit in the formula?

Its manual equivalent is CTRL+END+UP from the last row of a sheet where Range("A" & xx) represents column A.

Please refer this article for ways to find the last row. It is third item in the article.

VBA Code To Find Last Row Or Last Column (Best Way)

Discover the best methods to make your VBA code dynamic by coding in a way that allows your macros to automatically expand and collapse based on the size of each individual data set. This can be done by finding the Last Row or Last Column of your particular data range.

www.thespreadsheetguru.com

 

[Eloise T]

Its manual equivalent is CTRL+END+UP from the last row of a sheet where Range("A" & xx) represents column A.

Please refer this article for ways to find the last row. It is third item in the article.

VBA Code To Find Last Row Or Last Column (Best Way)

Discover the best methods to make your VBA code dynamic by coding in a way that allows your macros to automatically expand and collapse based on the size of each individual data set. This can be done by finding the Last Row or Last Column of your particular data range.

www.thespreadsheetguru.com

Thank you!