Text-To-Columns Fixed Width

When using Text-To-Columns, Excel "recognizes" that the data fits the Fixed 
Width criteria and PRE-assigns the width.
In most of the cases I work with Excel is wrong 99% of the time.
Is there a way to force Excel to NOT pre-assign the width (leave everything 
blank)?
0
Utf
2/4/2010 12:50:01 AM
excel 39879 articles. 2 followers. Follow

2 Replies
2678 Views

Similar Articles

[PageSpeed] 56

On the first panel of the wizard click Delimited, then on the second
panel click space as the delimiter.

Hope this helps.

Pete

On Feb 4, 12:50=A0am, TP <T...@discussions.microsoft.com> wrote:
> When using Text-To-Columns, Excel "recognizes" that the data fits the Fix=
ed
> Width criteria and PRE-assigns the width.
> In most of the cases I work with Excel is wrong 99% of the time.
> Is there a way to force Excel to NOT pre-assign the width (leave everythi=
ng
> blank)?

0
Pete_UK
2/4/2010 1:59:14 AM
Thank you for the response.
However, the data I would like to parse is in the following format:
9        08890    DATE TIME START_OF_TEXT, MORE_TEXT, MORE_TEXT, MORE_TEXT, 
MORE_TEXT, MORE_TEXT, MORE_TEXT, MORE_TEXT, MORE_TEXT, MORE_TEXT
The first 3 fields are fixed width and the subsequent fields are comma 
delimited (with spaces).
I need to keep the TEXT together and apply Text-To-Columns for the first 3 
fields.
If I use a delimiter, space, then everything will be separated.
If I use "Fixed Width" Excel decides to separate the TEXT as well (and I 
have to go through the exersice of removing the separations.
I would prefer to have Excel not "guess" where the data should be parsed, so 
that I can enter my own.

Hope this makes sense

"Pete_UK" wrote:

> On the first panel of the wizard click Delimited, then on the second
> panel click space as the delimiter.
> 
> Hope this helps.
> 
> Pete
> 
> On Feb 4, 12:50 am, TP <T...@discussions.microsoft.com> wrote:
> > When using Text-To-Columns, Excel "recognizes" that the data fits the Fixed
> > Width criteria and PRE-assigns the width.
> > In most of the cases I work with Excel is wrong 99% of the time.
> > Is there a way to force Excel to NOT pre-assign the width (leave everything
> > blank)?
> 
> .
> 
0
Utf
2/9/2010 2:21:01 AM
Reply:

Similar Artilces:

WorkOrder's (deleting, printing fullpage and 40 column)
1. Is there anyway to delete a workorder from the POS? 2. Initially I would like to setup the workorder to print the fullpage receipt. I was able to do this by pointing the receipt format to the fullpage receipt. When a customer comes into pay the workorder, say for instance with a cc I want it to print a regular 40 column receipt. How can this be done? I have tried recalling the workorder then changing the workorder to a sales transaction by pressing Ctrl-F1 again but it gives me an error 'Invalid Operation 437: An invalid transaction type was detected.' TIA Robert Robert, 1. Re...

Human Resource
Is there a way to change the number of decimal places for the Fixed Premium Amount / Benefit Rate field in HR. I need to create a Life Insurance code which calculates Premium at $0.149 per 1000 units of coverage (coverage foe me = 1 X Annual Salary rounded to nearest 1000). I cannot have Fixed Premium with a rate of 3 decimal places and Increment Units as 1000. I have tried a work aound as follows but that is not working either.... Workaround is as follows: If I put increment units as 10,000 and use a premium rate of $1.49, then I cannot enter coverage amounts rounded t...

Add text to Memo field
Hi All, I have 2 check boxes. If you check the box the on lick event adds text to a memo field. This issue is the last box checked text overrights the first. How would I add the additional text without removing the first? If me.ckbox1 = -1 then me.memo1 = "TEXT 1" end if If me.ckbox2 = -1 then me.memo1 = "TEXT 2" end if -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200703/1 That worked great. One more question if the user unchecks the box is there a way to find a...

Using VLOOKUP to Sum values from multiple columns
I am trying to use VLOOKUP to sum multiple values from different columns. I need this because the value is given by name of machine, and in the column directly right of that the cost is given. The table looks like this. 2010 2011 A B C D M086-1 7000 M105 10000 M105 5000 F120 9200 F120 2500 M086-1 7600 F102 1500 F102 5500 It goes on like this for a list of over 100 machines, and there are about 10 columns. So i need to sum the values for each machine over these columns. Say for instance i want to get the total...

repeated text
how can i find repeated text on a spreadsheet, for example, i have a list of names that and i always update it with new names. Some of the names are repeated. how can i find the repeated names without looking in the entire spreadsheet myself? See: http://www.cpearson.com/excel/duplicat.htm -- Regards, Peo Sjoblom "maria" <maria@discussions.microsoft.com> wrote in message news:5129508F-9A3A-4A77-A8B7-E975831F2DCF@microsoft.com... > how can i find repeated text on a spreadsheet, for example, i have a list of > names that and i always update it with new names. Som...

Circular text
How do I create circular text? ...

Macro to convert a number to text (English only)
The macro shown below will convert any Long number to the text equivalent (both +ve and -ve). Thanks must be given to: http://exceltips.vitalnews.com/Pages/T0351_Numbers_Spelled_Out.html who have the original version of this function. The original version was limited to +ve numbers between 1 and 999999. As such, it did not handle millions (which I wanted). I've changed the macro to handle both millions and billions. As it was easy to do, I also added the functionality to ahndle -ve number (as it simply adds a 'Minus' string before the number). A minor change I made to the ori...

Formula: values in one column based on corresponding data
My goal is simply this - I want to add a series of values (hours and minutes) worked on specific projects within one months time. Now, the month/day/year is listed in a separate column. Thus, one column lists all the hours and minutes used to complete a project and now I want to add up those values for a specific month's time. The reason is so I can explain how much time was spent doing a specific task for various cases. I believe the formula starts with "=sumif(" but after that I simply do not know. SUMIF won't suffice as you have multiple conditions to satisfy fo...

wrap text in a chart data table
Hi, Im have created a graph, which ive had no problem, but i when i click the option to "show data table" i cannot get the titles to wrap to the box, i have searched but i cannot find an option to wrap the text as i need it for a presentation. I do not want to just simply alter the font size if possible. Is there anyway of doing this? Data tables in charts are not flexible, either regarding their contents or their formatting. You could create a table in the cells near the chart, include what you want in it, and format it however you like. - Jon ------- Jon Peltier, Microsof...

Matching text in a cell to a list of values created by a formula
Hi Gents, Hoping this makes sense... I have a list of team names, where the results of the list were create by a formula: =IF(F2>=D2, E2, C2). The score of the game determine which team is returned. Another worksheet contains a list of participants, with the team the picked that week. I want to match the team they picked to the list o winners on the previous sheet. If it is found, return "Winner". I not found, return "Loser". This is the second formula I used =IF(ISNUMBER(MATCH(C2,Schedule!$G$2:$G$17,0)),"Winner", "Loser"). M problem is it ma...

how do i trace back my sort command on a column ?????
i had Example: student names ,parent names ,room #s .,if i sort only student names and accidentally save the excel sheet before shutting down the computer @ the ed of the day how can i undo sort the next day to unsort ,since the column is not corresponding with the parent's columns now ??please help asap,t o track back and save as before. In article <4569D329-4B1A-4EC4-9771-693CCDF9989C@microsoft.com>, rp@discussions.microsoft.com says... > i had Example: student names ,parent names ,room #s .,if i sort only student > names and accidentally save the excel sheet before sh...

How can I sort text (in excel) while ignoring articles (e.g., the
I work in a library and want to sort lists of CD titles. I want to sort the list alphabetically while ignoring articles (e.g., the, a, an) when they appear as the first word in the title. Hi I don't think you can do this. what might work is to include the names in an adjacent column an eliminate the articles, then sort on that. For example, this formula will eliminate the words "The ", "An " and " " from the left of the text in cell A1 (note the addition of a spac after the word in the formula, to prevent all words begining with "A being cropped): ...

Format Row based on text value in cell
I want to format a row based on the value of one of the cells in the row. Basically, I want to set the color of the row to green if a particular cell in the row is COMPLETE and red otherwise. How can I do this? Thanks, B. Search for "Conditional Formatting" in Excel help. Also, you can find help for Conditional Formatting on Chip's website, http://www.cpearson.com/excel.htm Hope that helps! Eric Maybe you'll like to experiment with this as well .. Assume the target row is row2, the particular cell in the row is B2, and the spec "COMPLETE" means B2 contai...

How to align the bottom lines of 2-column pages?
I have compiled a bi-lingual dictionary (left to right and right to left) using Word 2007; with the result that the bottom lines of a 2-column page cannot be aligned; understandably so. Now, I want to import the documents to Microsoft Publisher 2007, where I know that aligning those bottom lines together can be done - but how? Any help offered will be much appreciated! How are you planning to export the text from Word? How many columns per page? Will your dictionary be in booklet form? Are your definitions in two different Word files? -- Mary Sauer http://msauer.mvps.or...

8007000B Error, nothing works to fix
Hi, I have tried everything I´ve found on this web and the internet but I still get the error. To make my life harder, I can´t find my Toshiba Reinstall DVD I have Norton 360 on the system (tried to disable but no luck) I also have PerfectDisk 10 on the system Any clue of what to do ? (apart from a full reinstall) This is the log : 2010-05-29 17:51:43:646 1332 17e4 Agent ************* 2010-05-29 17:51:43:646 1332 17e4 Agent ** START ** Agent: Finding updates [CallerId = AutomaticUpdates] 2010-05-29 17:51:43:646 1332 17e4 Agent ********* 2010-05-29 17:51:43:646 ...

Can't see window toolbars and cant access switchboard -how 2 fix?
A designer took off the ability to use the main toolbar or view the switchboard, therefore can't update linked tables now that we're moving the database to a new server. Is there a way to re-enable these toolbars or update the link to the new location a different way? Thanks Try pressing and holding the Shift key while opening the database. Unless the designer removed this option you will by able to bypass the startup options, which means you will see the default toolbar and the database window, and will be able to make design changes. "Suzanne" <Suzanne@discussio...

Comparing Lists of text using colors or by other means
I have two lists of account names (for example). 500 names in column A and 1000 names in column B. I would like to highlight or somehow alter the names in column B that are in Column A without using the find and replace function.. I do not want to do it one at a time but cannot figure out how to do it with the entire range all at once? Goal is to then use the data associated with the highlighted cells in column B for reports etc. You could use a formula in column C that would indicate if the value in column B appears anywhere in column A. =isnumber(match(b1,a:a,0)) And drag down as far...

Replying in HTML to a plain or rich-text message
Is it possible to make Outlook convert my reply of a plain text or rich text message into an html message. I am doing this so that I can include text with colors other then black. Any ideas? Thanks Taskman anonymous@discussions.microsoft.com <anonymous@discussions.microsoft.com> wrote: > Is it possible to make Outlook convert my reply of a > plain text or rich text message into an html message. I > am doing this so that I can include text with colors > other then black. Any ideas? Thanks Sure. Reply to the mesage and then click Format>HTML prior to typing your res...

need formula for multiple columns/rows
I have a spreadsheet with a budget value in column C, monthly billings in columns D - Q, and the remaining balance in column R. I can use =c3-sum(d3:q3) for row 3, but is there a formula to repeat the calculation for multiple rows without having to type that into the R cell on every row. I'm sure it is simple, I'm just learning. Thanks, kzas Hi Kzas, If I got it right, you want the formula to calculate the sum for al the rows from row 3 up to the row is appears. If so, then the followin should do it: =$c$3-sum($d$3:$q3) -(assuming that there is only one budget figure)- =$c$3-su...

Pivot tables and column widths
Is there a way to permanently reset the column widths in a pivot tables? I am able to change the column width, but it defaults back to a standard width each time I refresh the pivot table. Can I set it up so that it will stay at a width defined by me? Thanks! In EXCEL 2007 try this:- 1. Right click the Pivot Table / PivotTable Options . . . / Layout & Format tab / remove the tick from:- Autofit column widths on update OK If my comments have helped please hit Yes. Thanks "Krissy" wrote: > Is there a way to permanently reset the column widt...

Automatically fill text box with yesterday's date
I have an unbound text box. Format to short date. How would I automatically have this filled with yesterday's date. I've tried date-1 in the Control Source and in the Default Value, but neither works. Any help would be appreciated. Thanks. "p-rat" <osupratt@yahoo.com> wrote in message news:09848f03-6ec3-4469-aabc-438ab7760529@f3g2000hsg.googlegroups.com... >I have an unbound text box. Format to short date. How would I > automatically have this filled with yesterday's date. I've tried > date-1 in the Control Source and in the Default Value, but neithe...

How to add columns to the end of a spreadsheet--(last col is IV)?
I've inherited a spreadsheet that has an ending column labeled as IV--somehow everything after that column was deleted. This spreadsheet is used to track my staff's daily time activity for the year and I need a column for each workday. It currently ends in the middle of October. I need to add/insert columns for the rest of October, November, and December. I'm trying to insert new columns, but I keep getting the message: Excel cannot shift nonblank cells off the worksheet. I've followed the instructions to delete the rows and columns from my last nonblank cel...

Convert Numbers to Text
Hi, I use the code available from the Microsoft Web site that converts numbers to text and it works very well however, I have had requests to add the word "and" in between the thousands and hundreds : example Instead of "One Thousand Two Hundred Fifty Nine Dollars and Ten Cents" as it now shows I Need "One Thousand Two Hundred and Fifty Nine Dollars and Ten Cents With my limited knowledge I realise that you have to add the word "and" to somewhere in the existing code, just not sure where ?? Thanks i'm not sure what you're using...

Conditional formatting across entire column
Is there a way to apply conditional formatting if any value in a entire column equals another single cell?? Thanks >Conditional formatting across entire column Do you mean across a row or down a column? I'll assume you mean across a row Assume the range of interest is A1:E1 Select the range A1:E1 Goto Format>Conditional Formatting Formula Is: =MAX(COUNTIF($A1:$E1,$A1:$E1))>1 Click the Format button Select the style(s) desired OK out -- Biff Microsoft Excel MVP "David Johnston" <jhh@thd.com> wrote in message news:5b001db1a5914238b035813a47314af5@ureader.co...

What Am I Doing Wrong Re Some Simple Math On A Column ?
Hello, Using Windows 7 Have a long column of numbers in Column A, with a blank column, Column B next to it. I wish to have Column B filled with the numbers in Column A divided by 1000. So, I put in B1: =(A1/1000) And then I thought I would just drag it downward for all the other rows Keeps coming back with an error (before any dragging). (Column A is Formatted as "Numbers") What am I doing wrong, please ? Thanks, Bob Bob, Probably, Excel still thinks those numbers in column A are text. Numbers align right and text aligns left in a cell. You can convert "text" numb...