Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to Columns Question
I have 6 fields of data all in column A but each row the data is spaced out
differently, not even close. The fields themselves do not have any spaces but obviously there various spacing beteen the fields. The first feild is flush left but all the others are all over the map. Is there a way I can prepare the data so it's sumwhat lined lined up enough to get a accurate Text to Columns without hacking off some text in some rows? It's 17k+ rows. TIA Dennis ============== |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to Columns Question
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to Columns Question
Field 1 is 24-60 characters long. I would like field 2 to start 80 characters
from position 1 of field 1 Field 2 is 4- 11 characters long I would like field 3 to start 20 characters from position 1 of field 2 Field 3 is 1-6 characters long I would like field 4 to start 20 characters from position 1 of field 3 Field 4 is 1-6 characters long I would like field 5 to start 20 characters from position 1 of field 4 Field 5 is 8 characters long I would like field 6 to start 20 characters from position 1 of field 5 Field 6 is 8 characters long Thanks for the help Ron, I hope this is the info needed. Dennis ============== In article , Ron Rosenfeld wrote: On Thu, 29 Dec 2005 18:49:16 GMT, rm the XX's (Dennis) wrote: I have 6 fields of data all in column A but each row the data is spaced out differently, not even close. The fields themselves do not have any spaces but obviously there various spacing beteen the fields. The first feild is flush left but all the others are all over the map. Is there a way I can prepare the data so it's sumwhat lined lined up enough to get a accurate Text to Columns without hacking off some text in some rows? It's 17k+ rows. TIA Dennis ============== If you construct a rule (or set of rules) to define where one field ends and the next begins, I'm sure we could construct a macro or formula solution to split each record into columns. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to Columns Question
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to Columns Question
Ron's suggestions are sound.
One way to visualize the data correctly (if you are ever in this situation again) is to change to a non-proportional font. Highlight the column and choose a font such as Courier with a size suitable to fit all the characters on screen. Pete |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to Columns Question
Im missing something. If I use the wizard the text text gets sliced up because
it some rows line up with other rows. I need to get bigger spacing so I can put the divider lines in. In article , Ron Rosenfeld Since you have the starting point of each field, it should be trivial to use the built-in text functions to parse it out. Or you could use the Data/Text-to-Columns wizard and use a fixed width for each field -- i.e. 79 characters for field 1 and so forth since each field, from your description, will always start at a fixed point. For formulas: Field1: =trim(left(record,60)) Field2: =trim(mid(record,80,11)) Field3: =trim(mid(record,100,6)) and so forth. --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to Columns Question
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to Columns Question
Thanx Ron and Pete, the courier font did it!!! I appreciate the help and
patience. Dennis ============ In article , Ron Rosenfeld wrote: For formulas: Field1: =trim(left(record,60)) Field2: =trim(mid(record,80,11)) Field3: =trim(mid(record,100,6)) and so forth. --ron Are you using a non-proportional font? Something like Courier or Courier New? If the data is as you have described it, things should line up. You can still use the formulas instead. If the data is not how you described it, you are going to have to describe how you can discern the dividing point between each field. --ron |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to Columns Question
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert from text to columns automatically on import? | Excel Discussion (Misc queries) | |||
comparing columns of text (cross-searching) | Excel Discussion (Misc queries) | |||
Messy Text to Columns | Excel Discussion (Misc queries) | |||
Text to Columns - moves text up | Excel Discussion (Misc queries) | |||
text from one column into multiple columns | Excel Discussion (Misc queries) |