| Home |
| Search |
| Today's Posts |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to subtract one text column based on their text column MS xls. | Excel Discussion (Misc queries) | |||
| Find a text from a column in a text string within another column? | New Users to Excel | |||
| Convert a Number Code to a Text Code | Excel Discussion (Misc queries) | |||
| Wrap text in column headers to fit text in column | Excel Discussion (Misc queries) | |||
| VBA code to define nbr of text character a a cell/column | Excel Programming | |||
Threaded Mode
