About Us

robs3131

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to subtract one text column based on their text column MS xls.	Salahuddin	Excel Discussion (Misc queries)	4	October 27th 08 04:57 AM
Find a text from a column in a text string within another column?	Mike Garcia[_2_]	New Users to Excel	1	October 22nd 08 06:50 PM
Convert a Number Code to a Text Code	Traye	Excel Discussion (Misc queries)	3	April 6th 07 09:54 PM
Wrap text in column headers to fit text in column	MarkN	Excel Discussion (Misc queries)	10	November 11th 05 04:21 AM
VBA code to define nbr of text character a a cell/column	James[_35_]	Excel Programming	0	March 18th 05 05:52 PM

Menu

About Us