• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Converting one column of text into two columns.

Eloise T

Active Member
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!
 

Attachments

Last edited:

AlanSidman

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

Code:
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

Excel Ninja
It can be done with formulas as well.

Below is basic example of code that should work, edit it to suit your needs.
Code:
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

Excel Ninja
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 :​
Code:
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

Active Member
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 :​
Code:
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:

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

Eloise T

Active Member
It can be done with formulas as well.

Below is basic example of code that should work, edit it to suit your needs.
Code:
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!
 
Last edited:

Eloise T

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

Code:
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

Excel Ninja
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

Well-Known Member
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

Well-Known Member
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

Excel Ninja
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

Active Member
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

Excel Ninja
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.
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
 

Eloise T

Active Member
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.
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

Active Member
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:
 

Attachments

Marc L

Excel Ninja
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

Active Member
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

Excel Ninja
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.
 
Last edited:

Eloise T

Active Member
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.
Thank you!
 
Top