Formatting after import question/problem

Posted by Bill T on September 03, 2000 10:28 AM

I use a spreadsheet that has one column for invoice numbers and another for the physical area the invoice is located.

Daily I get a huge text file from the mainframe which is too large for Excel, so it starts out in Access. I then export certain data into Excel.

I have to change the text invoice to numbers by first formatting the cells to numbers and then have multiply the cells by 1 to strip a leading zero to get them to sort correctly with "yesterdays" invoice numbers.

Now for the problem. The text for the area looks the same, for example, AR for Accounts Rec. It will sort OK with the old data. But on a pivot table or in a chart using a countif formula, the new AR is not recognized as the same as the old AR, i.e., the pivot table will report 14 AR's of "yesterdays: data, and 16 AR's of "todays" data.
I can clear the contents of the new cells and type in AR and copy and paste to the new cells, but I have a lot of areas and it's a pain.

Why is the new text AR seen differently from the old AR? And is there another way to fix this besides the manual change?? Simply formatting all cells in the column as text does not do anything.

Thanks for any advice you can give me.

Bill

Posted by Ivan Moala on September 07, 0100 3:34 AM

Re: Dang. Didn't work.

long shot here but....
What version of Access and Excel are you using ??
or to be more specific what file format is excel
saved in ?? are they the same ?? as diff versions
of access / excel can give diff results in the formating.

ivan

Posted by Thomas Venn on September 07, 0100 9:36 AM

Re: Dang. Didn't work.

One of the ways to see if you data is text or numbers is by trying this: in the fields where you suspect the data to be text, replace the data by typing 2 in one cell and 3 in another cell directly below, then in the next column, sum up 2 and 3 (I.e., =sum(a1:a2)). If your result is zero, then the cells are text. If your result is 5, then the cells are not text cells. for some reason, there are differences in a "true" text cell and a "formatted" text cell. you can get "true" text cells when you parse the data by going to Text to Columns. However, when you use the Format command to format it as text, it only looks like text, but really is still a number. weird and confusing. give it a try in column A by typing 2 in Cell A1, and 3 in Cell A2, then use the highlight your entire Column A and use the Text to Columns command and in step 3, choose Text. This should illustrate the differences.

On the part of Access Export, you might want try File->Save As / Export -> Save as External File -> Choose your Save As type as a Text File. Then open this file in Excel. It is worth a shot. : This indeed is kinda odd. do you perhaps have a space before AR or after AR. this might be one reason. :

Posted by thomas venn on September 05, 0100 12:46 PM

One type of your AR is TEXT, while the other type of AR is GENERAL (NUMBER format). Highlight your column the parse. do this by going to Data->Text to Columns...-> and choose either TEXT format or GENERAL format.

Cheers,

Thomas

Posted by Bill T on September 06, 0100 4:51 PM

Dang. Didn't work.

I tried it, but the only way I was able to make it work was to again type in (for example), AR, copy and paste down the column, and then do the pivot table. It just wouldn't recognize the conversion and treated them differently.

To make this even more frustrating.. this spreadsheet is shared among 3 users. One of the columns is for social security numbers. On 2 of the PC's, I can format these as SS #'s, but I have to enter a cell, hit F2 and enter, before it will change to the correct format. On the 3rd PC, I format the cells as SS#'s and voila, they all change to the correct format immediately. Oh wait, that one uses Rhumba and the other two use Extra Attachmate. Hmm. Maybe a clue there.

Posted by Bill T on September 07, 0100 5:27 PM

I'll try this manana..

I'll try this tomorrow. I'm using 97 at work. Another odd thing. I looked at the version stuff from Ivan's hint. I had not bothered saving the export Excel file before, but today I did. I got a message that this version of Excel was not the up to date version and asked if I wanted to convert, which I did. To no avail as far as my problem went. But if I have only one Excel and one Access and they are from the same CD, why in hell would it export to a "different" version? I have only one freaking version.

Posted by thomas venn on September 06, 0100 5:47 PM

Re: Dang. Didn't work.

This indeed is kinda odd. do you perhaps have a space before AR or after AR. this might be one reason.

Posted by Bill T on September 06, 0100 6:37 PM

Re: Dang. Didn't work.

No visible space at all. The field from which AR comes is extracted from an Access database query that exports the results to Excel. I have supposed that that shows up as text, but I don't know how to verify that. I have tried every method I can think of to insure that all cells are the same. I even entered AR into an empty cell, copied the format with the paintbrush, and pasted the format. Nada. The numbers work after I multiply them x1. I suppose this does the same thing as highlighting the cell, pressing F2 and enter?? I don't know how to do something similar to text to "force the issue".

Posted by Bill T on September 05, 0100 8:25 PM

Thanks Thomas. I'll do that first thing tomorrow AM. Been driving me crazy.

Bill