Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Georgetown, Grand Cayman, Cayman Islands
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Cell Format (2003)

    I'm trying to create a custom cell format to take a 25-character alphanumeric string and display it as XXXXX-XXXXX-XXXXX-XXXXX-XXXXX you may recognize this as a windows or office product code.

    My current asset management system is a spreadsheet, has purchase date/price, it's computer name, which division, which office and who it's assigned to but also has license info/product codes. I suppose I could just enter it with the - in it, but I was curious to see if it could be done as a 25-character string and displayed "custom" like a phone number or zip code+4 special format.

    I tried #####-#####-#####-#####-##### but the # is only for numbers. * didn't work either, excel uses * for filling the cell width with a character..

    Is this something I could even do, or am I just whistling in a hurricane?

    -M

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Cell Format (2003)

    Cell formats in Excel are for numbers only, not for alphanumeric strings.
    Unfortunately, you cannot enter 25 digit numbers in Excel, or rather, you can but only 15 digits are stored.
    You can, however, use a formula to display a formatted string in another cell, for example an adjacent cell.
    With a 25 digit alphanumeric code in cell A1, the formula
    <code>
    =LEFT(A1,5)&"-"&MID(A1,6,5)&"-"&MID(A1,11,5)&"-"&MID(A1,16,5)&"-"&MID(A1,21,5)
    </code>
    will display the string in groups of 5 characters separated by hyphens. The formula can be filled down.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Cell Format (2003)

    Hi Mark

    I think you have discovered that when you add a letter, the whole entry becomes text. You can't format Text using a format. You will need to put a formula in an adjacent column and do something like:

    =LEFT(A1,5)&"-"&MID(A1,6,5)&"-"&MID(A1,11,5)&"-"&MID(A1,16,5)&"-"&MID(A1,21,5)

    You can put the product code in as 1234567890ABCDEFGHIJ12345 and it will return 12345-67890-ABCDE-FGHIJ-12345

    HTH
    Jerry

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Cell Format (2003)

    Hi Mark
    As Hans and Jerry have pointed out, formatting is not an option. If you decide to solve this with formulae you may want to place the input and output on different sheets to avoid cluttering the display. It will also be necessary to ensure that the formulae on the display sheet cover an adequate range to satisfy all input. An alternative approach is to either create code which ensures the display sheet is properly populated with formulae; or create a user defined function which fires each time a cell is changed and if necessary converts the 25 character alpha-numeric string to hyphenated 29 character string.

    Post back if you wish to pursue either of these options and need assistance.
    Regards
    Don

Posting Permissions

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