Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Varying Column Widths for Text Import
Hi all,
I have limited experience with VBA, so I decided to seek some help here. I need to import a text file into Excel, it does not contain commas, nor special characters. I have 3 different layouts given to me by the user, for example Format 1: Field 1. 3 chars Field 2. 20 chars Field 3. 13 chars So the 3 different layouts have different column widths. The text file is such that it loops from Format 1 (1 line) to Format 2 (20++ lines) to Format 3 (10+ lines) and then back to Format 1. The only way to identify which format each line belongs to, is to search for certain positions where certain text are present within the line. for example, format 1 contains non-empty char in position 3 to 10, format 2 contains NULL char in positions 14 to 30, format 3 contains 9s from postion 25 to 29... I've recorded the macro to do the importing, but cannot seem to find the code to tweak the varying column widths. any help is greatly appreciated!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Varying Column Widths for Text Import
Hello,
Well, you don't necessarily need any VBA here, though it depends how easily these different layouts can be identified. Excel has quite useful text functions for these kinds of purposes. I did not understand what you mentioned about NULL characters (layout 2). Did you mean that each of the layouts has fixed length? Then you could test for length of the column using LEN. If column width is not fixed with each layout, you can use FIND for identification: 1) NOT(ISERROR(FIND("9";A1;25))) - if TRUE = belongs to layout 3 - "9" is the text to be searched, can be longer like "ABC" Then you have to check whether the line belongs to layout 1 or 2 similarly. You only need to search either for layout 1 or layout 2, as if you know that "not layout 3" AND "not layout 2" = "layout 1" After you have tested which line is which, you could use LEFT or MID command to take the characters you need. This can be done by using IF -function. Hope this helps. Best Regards, "Rytrilius" wrote: Hi all, I have limited experience with VBA, so I decided to seek some help here. I need to import a text file into Excel, it does not contain commas, nor special characters. I have 3 different layouts given to me by the user, for example Format 1: Field 1. 3 chars Field 2. 20 chars Field 3. 13 chars So the 3 different layouts have different column widths. The text file is such that it loops from Format 1 (1 line) to Format 2 (20++ lines) to Format 3 (10+ lines) and then back to Format 1. The only way to identify which format each line belongs to, is to search for certain positions where certain text are present within the line. for example, format 1 contains non-empty char in position 3 to 10, format 2 contains NULL char in positions 14 to 30, format 3 contains 9s from postion 25 to 29... I've recorded the macro to do the importing, but cannot seem to find the code to tweak the varying column widths. any help is greatly appreciated!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Varying Column Widths for Text Import
But you didn't describe each field for Format 2 and format 3.
Maybe you could base it on the sum of the lengths of the fields (format 1 has a combined length of 36 characters). If this sounds interesting, can any of the fields be empty? can any of the fields be shorter than the given length (can field 3 of format 1 be 1-12 characters)? Maybe you could just add another column of cells that determine the format type. Then use that in other formulas to parse each field: Column B: =IF(LEN(A1)=36,1,IF(LEN(A1)=59,2,3)) (whatever the lengths may be...) Then in column C, grab the first field: =IF(B1=1,TRIM(MID(A1,1,3)),"somethingelse") in column D, grab the second field: =IF(B1=1,TRIM(MID(A1,4,20)),"somethingelse") and so forth. If you can figure out a formula that goes in column B, it should work! Rytrilius wrote: Hi all, I have limited experience with VBA, so I decided to seek some help here. I need to import a text file into Excel, it does not contain commas, nor special characters. I have 3 different layouts given to me by the user, for example Format 1: Field 1. 3 chars Field 2. 20 chars Field 3. 13 chars So the 3 different layouts have different column widths. The text file is such that it loops from Format 1 (1 line) to Format 2 (20++ lines) to Format 3 (10+ lines) and then back to Format 1. The only way to identify which format each line belongs to, is to search for certain positions where certain text are present within the line. for example, format 1 contains non-empty char in position 3 to 10, format 2 contains NULL char in positions 14 to 30, format 3 contains 9s from postion 25 to 29... I've recorded the macro to do the importing, but cannot seem to find the code to tweak the varying column widths. any help is greatly appreciated!! -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Varying Column Widths for Text Import
Thanks Joe and Dave,
The user's file is actually nicely padded with spaces (NULL) and zeroes so all the rows are of the same length. I will try both your suggestions and see if i can achieve anything. Thank you very much!! Regards, Rytrilius -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200511/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF/AND/OR/DATEIF Issue...sorry...long post... | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
I wish to have multiple column widths for a the same column. | Excel Worksheet Functions | |||
Changing column widths w/o affecting all rows | New Users to Excel | |||
Two column widths for the same column | Excel Worksheet Functions |