Results 1 to 4 of 4
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    I want to add a text prefix as a custom cell format.

    For example, whatever the cell contains, e.g. xxx, I want it to display as MAKE xxx
    So, if the cell has a value 36.5, I want it to display as MAKE 36.5
    If the cell has 37, I want to display as MAKE 37
    If the cell has 46W, I want to show as MAKE 46W
    (in an ideal world, if the cell is empty or blank, I would like it to display MAKE unassigned)

    If I create a custom format as "MAKE" 00
    ..it displays MAKE 37 when the cell contains 36.5

    Any suggestions would be gratefully received.

    zeddy

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    This custom format: "Make" 00.0;[Red]"Make" 00.0;"Make Unassigned"
    Will handle all your conditions with exception of the Blank cell. Of course you have to live with the .0 on whole numbers

    Using ##.# vs 00.0 will get you 37. if you prefer instead of 37.0.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    As per RG's format, but
    To allow for the text entries you need to add the 4th Custom Format argument

    "Make " ##0.0;[Red]"Make " -##0.0;"Make Unassigned";"Make "@

    But I think if you want to have 37 as Make 37, 38.5 as Make 38.5, 23E as Make 23E and Blank or 0 as Make Unassigned
    Rather than Make 37.0, Make 38.5, Make 23E and Make Unassigned

    then I think you are going to struggle
    Andrew

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Many thanks to both of you!

    I've used a combo of your ideas:

    RetiredGeek:
    "Using ##.# vs 00.0 will get you 37. if you prefer instead of 37.0." ; yes, 37. for me is better than 37.0

    Andrew:
    "To allow for the text entries you need to add the 4th Custom Format argument
    "Make " ##0.0;[Red]"Make " -##0.0;"Make Unassigned";"Make "@
    This last bit was important, many thanks, - otherwise 23E would be shown as blank.
    I removed the surplus space in the (numeric) first parts. Otherwise I got an extra space with Make 38.

    So I ended up with:
    "Make" ##.#;[Red]"Make" -##.#;"Make Unassigned";"Make "@

    You are right, to get what I really wanted is a struggle.
    Who would have thought that a simple prefix would be such a pain.

    many thanks again
    very much appreciated

    zeddy


Posting Permissions

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