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
|