• 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

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:
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.
 
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
 
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 !
​
 
Marc L said:
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 !​
​
Click to expand...
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?
 
shrivallabha said:
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
Click to expand...
How can this be modified to cause EOD [end of data] to be 10 blank lines/rows?
Thanks!
 
Last edited:
AlanSidman said:
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.
Click to expand...
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.
 
Eloise T said:
There were some blank lines 114 and 115 rows down.
Click to expand...
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 …​
​
 
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.
 
How can this be modified to cause EOD [end of data] to be 10 blank lines/rows?
Thanks!
Click to expand...

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 said:
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"?
Click to expand...
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?
 
Eloise T said:
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?
Click to expand...
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
 
shrivallabha said:
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
Click to expand...
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?
 
Marc L said:
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 …​
​
Click to expand...
I updated with the mod.
Here's a sample of real data..see attached:
 

Attachments

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) …​
​
 
Marc L said:
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) …​
​
Click to expand...
Thanks for your assistance. I'll try that.
 
Eloise T said:
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?
Click to expand...
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.
www.thespreadsheetguru.com

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 www.thespreadsheetguru.com
 
Last edited:
shrivallabha said:
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.
www.thespreadsheetguru.com

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 www.thespreadsheetguru.com
Click to expand...
Thank you!
 
You must log in or register to reply here.
Back
Top