Difference between revisions of "Data Split"

From IARC 207 Wiki
Jump to navigation Jump to search
imported>Bob
 
imported>Bob
 
Line 1: Line 1:
Copy and paste the text in the box into the visual basic editor that comes with Excel.  To get access to personal.xls in the visual basic editor check out this article: [[Save_Macros_in_Excel]]
+
Copy and paste the text in the box into the visual basic editor that comes with Excel.  To get access to personal.xls in the visual basic editor check out this article: [[Save Macros in Excel]]
  
 
This short macro splits a cell (you can select all the rows you need to split and just run the macro once).  The split method splits based on tabs and commas.  If you want others (like spaces) or don't want tabs/commas then toggle the boolean variables in the macro.
 
This short macro splits a cell (you can select all the rows you need to split and just run the macro once).  The split method splits based on tabs and commas.  If you want others (like spaces) or don't want tabs/commas then toggle the boolean variables in the macro.
 +
 +
Once you've added this macro to your library you may also find it useful to create a [[Tool Bar Button|menu button]] for it.
  
 
  Sub Data_Split()
 
  Sub Data_Split()

Latest revision as of 11:30, 12 March 2008

Copy and paste the text in the box into the visual basic editor that comes with Excel. To get access to personal.xls in the visual basic editor check out this article: Save Macros in Excel

This short macro splits a cell (you can select all the rows you need to split and just run the macro once). The split method splits based on tabs and commas. If you want others (like spaces) or don't want tabs/commas then toggle the boolean variables in the macro.

Once you've added this macro to your library you may also find it useful to create a menu button for it.

Sub Data_Split()
'
' This short macro does a tab and comma text to columns separation using
' tabs and commas.
'
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
       TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
       Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
       :=Array(1, 1), TrailingMinusNumbers:=True
End Sub