• 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.

Turning Text to Column into a Function

T

Tim Hanson

Member
I have been trying to make a function for Text To Column such that I input its elements into a 2nd sub but I am just not getting how it all fit together

Thanks


Code: 
Sub Text ToColumn()
 
    fTextToColumns "Question", "9", "I", "["
 
End Sub

Code: 
Sub fTextToColumns(colHeader As String, InsCol As Long, Dest as String Delimiter As String)
  Dim aCell As Range
 
  Set aCell = Sheet1.Rows(1).Find(What:=colHeader, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
 
'~~> Set your sheet here
Sheets(3).Select
 
'~~> Set your columns here
Columns(InsCol).Insert
Columns(InsCol).Insert
 
'*************************************************
      Columns(aCell, Cells(1, aCell.Column)).Select
'*************************************************
                                          '~Set destination range here
Selection.TextToColumns Destination:=Range(Dest & 1), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="Delimiter", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
 
'****************************************
    Columns(aCell, Cells(1, aCell.Column)).Select.Delete
'****************************************
 
End Sub
 
Hi Tim ,

Can you specify what you wish to do ?

Excel can convert Text to Columns only one column at a time ; if you wish to find the column labelled Question , then the following statement is to be changed :

Columns(aCell, Cells(1, aCell.Column)).Select

Even otherwise , as it stands , it gives an error.

Narayan
 
Hello Narayan, I have the macro below I use for "Text To Column", which I am trying to alter as in the original question so I can call it like so:
Code: 
Sub Text ToColumn()
 
    fTextToColumns "Question", "9", "I", "["
 
End Sub

Where "Question" or ("H") is the original column that has the splitting that need to be done on it looks like: "Stuff1 [Stuff2]"
"9" is the column I am inserting a column in to twice one to hold "Stuff1" the other to hold "Stuff2]"
The Destination Column is "I1" (or "9")
The Dilimiter is "["

I am trying to learn more vba and thought this was a doable puzzle but have gotten stuck.

Thanks


Code: 
Sub TextToColumns()
Dim i As Long, k As Long, j As Integer
 
'Application.ScreenUpdating = False
 
'~~> Set your sheet here
Sheets(3).Select
 
'~~> Set your columns here
Columns(9).Insert
Columns(9).Insert
 
'*************************************************
      Columns("H:H").Select
'*************************************************
                                          '~Set destination range here
Selection.TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="[", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
 
'****************************************
    Columns("H:H").Delete
'****************************************
 
'Application.ScreenUpdating = True
End Sub
 
Hi Tim ,

Thanks for the clarifications ; let me check ; off-hand , don't you think there is some redundancy in the second and third parameters "9" and "I" ?

I would think that the routine you write should accept "I" as the second parameter , and the number of columns you want should be inserted as the third parameter ; in this case , the third parameter would be 2.

Another point is why do you want columns to be deleted ? As far as possible , any code which processes data should inflict minimum damage on the worksheet / workbook , unless these operations are absolutely essential to the end objective.

The less you change anything in a subroutine , the more situations it can be used in. If really the columns need to be deleted , the delete statement can always form a part of the calling routine.

Narayan
 
Thanks Narayan that was very helpful, The excel sheet will be the data source for Crystal Report so I want to delete all fields that will not be needed in CR. I will continue with all your comments in mind
 
Here is what I came up with

Where:
cNum is the column to be parsed
iDel is the parsing delimiter
iNN1 is the header name for the first added column with parsed data
iNN2 is the header name for the second added column with parsed data

Code: 
Sub ttc()
fttc "8", "[", "New Col Name1", "New Col Name2"
End Sub
 
Sub fttc(cNum As Long,  iDel As String, iNN1 As String, iNN2 As String)
  '~~> Set your sheet here
  Sheets(2).Select
  '~~>Adding Columns
  For colx = 1 To 2 Step 1
    Columns(cNum + colx).Insert Shift:=xlToRight
  Next
  '~~>Column to be parsed
  Columns(cNum).Select
  '~Set destination range here
  Selection.TextToColumns Destination:=Cells(1, (cNum + 1)), DataType:=xlDelimited, _
    TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=iDel, FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
 
    '~~>Delete original column
    Columns(cNum).Delete
 
    '~~>Name New Columns with the parsed data
    Cells(1, cNum).Select
    ActiveCell.FormulaR1C1 = iNN1
 
    Cells(1, (cNum + 1)).Select
    ActiveCell.FormulaR1C1 = iNN2
 
    Range("A1").Select
 
End Sub
 
If more then 2 columns will be added in the parsing then I came up with this version as I could not figure out how to add more new column header names that did not just add a arbitrary number of "iNN' variables.

The last parameter is the Sheet Number
You need to add your "New header names" at the bottom of the 2nd sub "Sub fttc2"

Where:
cNum is the column to be parsed
iCol is the number of columns to insert
iDel is the parsing delimiter
iSn is the Sheet Number

Code: 
Sub ttc2()
 
    fttc2 "8", "3", "[", "2"
 
    End Sub
 
    Sub fttc2(cNum As Long, iCol As Long, iDel As String, iSn As Long)
 
    Dim Headers As Variant
    Dim i As Long
    Dim BaseWks As Worksheet
 
    '~~> Set your sheet here
    Sheets(iSn).Select
 
    '~~>Adding Columns
    For colx = 1 To iCol Step 1
      Columns(cNum + colx).Insert Shift:=xlToRight
    Next
 
    '~~>Column to be parsed
    Columns(cNum).Select
 
    '~Set destination range here
    Selection.TextToColumns Destination:=Cells(1, (cNum + 1)), DataType:=xlDelimited, _
      TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
      Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
      :=iDel, FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
 
    '~~>Delete original column
    Columns(cNum).Delete
 
    Set BaseWks = ThisWorkbook.Worksheets(iSn)
 
    'Set New Col Header Names, add as many name as iCol is equal to
    '*****************************************************************************
        Headers = Array("New Col Name1", "New Col Name2", "New Col Name3")
    '*****************************************************************************
 
      For i = LBound(Headers) To UBound(Headers)
      BaseWks.Cells(1, i + cNum) = Headers(i)
      Next i
 
 
    End Sub
 
You must log in or register to reply here.
Back
Top