Need help with text to column code
 

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

--
Robert

Need help with text to column code
 

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.

--
Regards,
Tom Ogilvy


"robs3131" wrote:

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

--
Robert

Need help with text to column code
 

On Jul 3, 12:38 pm, robs3131
wrote:
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

--
Robert

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

Need help with text to column code
 

Thanks guys for the info.
--
Robert


" wrote:

On Jul 3, 12:38 pm, robs3131
wrote:
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

--
Robert

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