Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell formatting (v2000)

    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

  2. #2
    Lounger
    Join Date
    May 2003
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell formatting (v2000)

    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.

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell formatting (v2000)

    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.

  4. #4
    Lounger
    Join Date
    May 2003
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell formatting (v2000)

    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.

  5. #5
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell formatting (v2000)

    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

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Cell formatting (v2000)

    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:

    '@',
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    New Lounger
    Join Date
    Sep 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell formatting (v2000)

    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.

  8. #8
    New Lounger
    Join Date
    Sep 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell formatting (v2000)

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

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Cell formatting (v2000)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •