Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
data feed text delimited
i have a the download statement
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, 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)) that gives me 04 Nov 100.0 (RUP KE-B),0.55,pc,0.40,0.55,0,289,04 Nov 100.0 (RUP WE-B),0,pc,25.60,26.10,0,0, some lines from the download site may be half blank and give me 04 Nov 100.0 (RUP KT-8),0,pc,0.45,0.60,0,0,,,,,,,, or ,,,,,,,04 Nov 100.0 (RUP WE-8),0,pc,0,0,0,0, the following starts the strip down and clean up the 1st part & 2nd part of data but if the fist half or second half are blank it mixes up the rest of program how would a I write a statement to say if there is nothing between the commas asume the download source is partly blank and treat that part of line as a blank instead of treating it as no data and stripping away in the formatting. Range(Range("A4"), Range("A4").End(xlDown)).TextToColumns Destination:=Range("A4"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)) Range(Range("M4"), Range("M4").End(xlDown)).TextToColumns Destination:=Range("M4"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)) thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying data to new workbook and saving as text delimited | Excel Discussion (Misc queries) | |||
Convert column data to semicolon delimited text string | Excel Worksheet Functions | |||
Extracting Numeric Data from a Delimited Text String | Excel Worksheet Functions | |||
how do Import data tab delimited text? | Excel Worksheet Functions | |||
Converting Excel data into semicolon delimited text file | Excel Discussion (Misc queries) |