Cell formatting (v2000)

Topic

New Reply

I want to format some cells with an apostrophe in the field then the number and another apostrophe and comma at the end. I have all the numbers in the fields already and want to have this formatting applied. EX: ‘12345’,
Any ideas how to do this?
thanks
ccj

Reply | Quote

Replies

Select cells to be formatted.
From the Format menu, select Cells.
On the Number tab, in the Category field, select Custom.
Click in the Type field and enter your format as follows:
‘#####’,
Click OK.

Reply | Quote

I tried that but it did not work. I wonder if there is something wrong with Excel on my PC – the “sample” box does not change when I try the different formats. What do you think?
Thanks again for your reply – I think that’s the way to do it.

Reply | Quote

My sample box does not change either. Perhaps it’s asking too much of Excel to display it the “customized” way you want it?
Regardless, what DOES happen to the cells? Anything?
Is there data in the cell before you apply the format?
When you format the cells, if you type in new data, what happens to the data? How is it formatted?

eta: Are your numbers longer than the designation you gave? For example, if you enter 123456789 in a cell that you’ve formatted as above, it will not be formatted in the way you desire. You need to insert enough pound signs to accomodate the longest potential data length.

Reply | Quote

I imported the numbers from Oracle and that may be the problem. When I started a new spreadsheet and just typed in numbers your method worked perfectly. I’m thinking the format or something in the date coming over from Oracle is the actual problem.
thanks for your help – it does work as you said.

Reply | Quote

Excel has an annoyance of treating imported numerical data from Access as text, there are several threads in this Forum on how to turn the data into numeric format that Excel can calculate on. I guess the same thing is happening with your Oracle data. Since however you want a text format, I presume you don’t need to see those threads, but note that you won’t be able to use the ‘@’, format data in calculations.

Reply | Quote

Excel has an annoyance of treating imported numerical data from Access as text, there are several threads in this Forum on how to turn the data into numeric format that Excel can calculate on. I guess the same thing is happening with your Oracle data. Since however you want a text format, I presume you don’t need to see those threads, but note that you won’t be able to use the ‘@’, format data in calculations.

Reply | Quote

I imported the numbers from Oracle and that may be the problem. When I started a new spreadsheet and just typed in numbers your method worked perfectly. I’m thinking the format or something in the date coming over from Oracle is the actual problem.
thanks for your help – it does work as you said.

Reply | Quote

My sample box does not change either. Perhaps it’s asking too much of Excel to display it the “customized” way you want it?
Regardless, what DOES happen to the cells? Anything?
Is there data in the cell before you apply the format?
When you format the cells, if you type in new data, what happens to the data? How is it formatted?

eta: Are your numbers longer than the designation you gave? For example, if you enter 123456789 in a cell that you’ve formatted as above, it will not be formatted in the way you desire. You need to insert enough pound signs to accomodate the longest potential data length.

Reply | Quote

My sample box changes if the numbers are not formatted as text. I think yoyophil is onto the problem, your numbers either have text in them, or the cells they are in are formatted as text. If so, see if this custom format does what you want:

‘@’,

Reply | Quote

John yours did the trick – ‘@’ does it. It was the formatting as you suspected.
thanks all for your help

Reply | Quote

John yours did the trick – ‘@’ does it. It was the formatting as you suspected.
thanks all for your help

Reply | Quote

My sample box changes if the numbers are not formatted as text. I think yoyophil is onto the problem, your numbers either have text in them, or the cells they are in are formatted as text. If so, see if this custom format does what you want:

‘@’,

Reply | Quote

I tried that but it did not work. I wonder if there is something wrong with Excel on my PC – the “sample” box does not change when I try the different formats. What do you think?
Thanks again for your reply – I think that’s the way to do it.

Reply | Quote

Select cells to be formatted.
From the Format menu, select Cells.
On the Number tab, in the Category field, select Custom.
Click in the Type field and enter your format as follows:
‘#####’,
Click OK.

Reply | Quote

ccj

When you say you have the numbers already in the fields do you mean JUST the numbers or do you have any apostophes or commas already entered. If your not sure, select a cell and look in your formula bar.
If you have just the number, say 12345 in cell A1, you should be able to do exactly what Cindi stated and the resulting formatting ‘#####’, should make your entry appear as ‘12345’,
Any apostrophes or commas already entered will screw up the formatting.

yoyoPHIL

Reply | Quote

ccj

When you say you have the numbers already in the fields do you mean JUST the numbers or do you have any apostophes or commas already entered. If your not sure, select a cell and look in your formula bar.
If you have just the number, say 12345 in cell A1, you should be able to do exactly what Cindi stated and the resulting formatting ‘#####’, should make your entry appear as ‘12345’,
Any apostrophes or commas already entered will screw up the formatting.

yoyoPHIL

Reply | Quote