Need help with text to column code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I'm working on automating a text to column processing of data -- I had done
this yesterday with a data set that had a symbol separating the data -- I had
recorded the macro and it seemed that the the code in the output was fine.

Today however, I am trying to automate this for a data set that is separated
by a space -- when I record the macro, it gives a bunh of arrays, the number
of which seems to depend on the number of records in the cell separated by a
space. My question is can I automate this with code given that the arrays
are dependend on the number of records in the cell? The code I recorded is
below. Thx!

Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=" ", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10,
1), Array(11, 1), Array(12 _
, 1), Array(13, 1), Array(14, 1), Array(15, 1)),
TrailingMinusNumbers:=True
 
The field info array specifies how you want each column handled. Look in the
help on TexttoColumns to see what the numbers mean. The differ in meaning
based on whether you are delimited or Fixed width.
 
Hi all,

I'm working on automating a text to column processing of data -- I had done
this yesterday with a data set that had a symbol separating the data -- I had
recorded the macro and it seemed that the the code in the output was fine.

Today however, I am trying to automate this for a data set that is separated
by a space -- when I record the macro, it gives a bunh of arrays, the number
of which seems to depend on the number of records in the cell separated by a
space. My question is can I automate this with code given that the arrays
are dependend on the number of records in the cell? The code I recorded is
below. Thx!

Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=" ", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10,
1), Array(11, 1), Array(12 _
, 1), Array(13, 1), Array(14, 1), Array(15, 1)),
TrailingMinusNumbers:=True
Click to expand...

Search the VBE Help for TextToColumns and read the parameter options.
Set the Space:=True. Also you can research the Parse or Split
Functions. As an aside, Chr(32) is the character for "space."

You can try the following:

Sub testIt()

Selection.TextToColumns Destination:=Range("A6"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False
End Sub

Matt
 
Thanks guys for the info.
--
Robert


[email protected] said:
Search the VBE Help for TextToColumns and read the parameter options.
Set the Space:=True. Also you can research the Parse or Split
Functions. As an aside, Chr(32) is the character for "space."

You can try the following:

Sub testIt()

Selection.TextToColumns Destination:=Range("A6"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False
End Sub

Matt
Click to expand...
 
Back
Top