Entering Cable sizes.

[BJC]

I have been copying cable sizes from one one data base ( Access) to Excell and it works for everything but "ought" size cables. If I copy a size 4/0 cable it comes out in excell as Apr-00, if I reformat the cell it becomes 36617.
I know if I put an ' in front of the 4/0 will show up just fine. I am sure there is a way to make it show up as $/0 but haven't stumbled on it yet.
Is there a way to make it happen?
Thanks
BJC

 

[TD2K]

Can you specify the cell(s) in which you are going to be copying the data to from Access as text in Excel (format, cells, number tag and then 'text' option)? That's what you are doing with the ' sign essentially.

 

[melone]

While Paste Special->Text work?

 

[abeltio]

i had in the past prepared cable lists and stumbled on the same thing...
the solution we adopted was...
format the whole column as text
right justify (so the numbers start from the right end of the cell and not the left as text)
use the following convention for the big cables:
0
00
000
0000 <- your 4/0 which is in reality what it means... 4/0 is short for 0000 (four zeroes)
HTH

saludos.
a.

 

[BJC]

Thanks to all. I had tried all those solutions - i just thought there might be an easier way.
If I copy a an area that is 12 columns wide with with 40 or 50 rows it copys text, numbers, etc correctly but not the 2/0, 3/0 etc. It seem like those characters are the only ones that I have to specifically format a row for. Not a big problem, I just thought I was missing an obvious trick.

Thanks to all again.
BJC

 

[cowski]

If you are copying the entire table or contiguous portions of it, you can export directly from Access to Excel. The X/0 entries will show up as text, no work necessary on your part.

If you are copying to an existing Excel workbook, this method won't be of much use.

 

[HNGRR]

Every time that you digit or paste a field that include the right slash ("/") in a a cell, you have problems whit a intrinsic tendency of EXEL to assume it is a date.
Few month ago, was publishied a smart trick that suggested digit SPACE as first entry.It works!

 

[BJC]

I think I solved the problem I was having.
The data I was working with was in 47 columns. when I copied the cable sizes ( and the data in the other 46 colums) to Excel the cable sizes showed up as dates. # 4/0 being Apr-00 etc. I am only using about 6 of the colums so I copy them to another section of the spreadsheet where they eventually get pasted on to a drawing.
I used some nested if statement which work because there are only four "naught" size cables.

=IF(K2=36617,"4/0",IF(K2=36586,"3/0",IF(K2=36557,"2/0",IF(K2=36526,"1/0",K2))))

K2 is the cell where the cable size in one of the 47 pasted columns is located, the if statement is in the cell where I want to copy it. The numbers 36586 are the serial numbers for April 1, 2000 etc.