+ Reply to Thread
Results 1 to 6 of 6

Custom Formatting for Cells with Numbers and A Letter

  1. #1
    Registered User
    Join Date
    01-14-2009
    Location
    Earth
    MS-Off Ver
    2007
    Posts
    43

    Custom Formatting for Cells with Numbers and A Letter

    How would I go about formating a cell which is made up of all numbers except for one character, which is a letter? I need it to look like this:

    Please Login or Register  to view this content.
    I have tried many things but nothing has worked and when I try:

    Please Login or Register  to view this content.
    It does not work. That is the closest I come to getting anything to work.

    This field is entered in numerous times and it would not be efficient to enter a formula each time or do anything tricky. Does anyone know if this is possible?

    Thanks :-)
    Last edited by VBA Noob; 01-14-2009 at 02:43 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582
    Custom formatting is applied to numbers and since you have a letter, it won't work. It might be possible with VBA. What exactly are you entering? e.g. do you always enter the entire 15 characters and you just want excel to put in the dashes? or sometimes do you enter 14?

    You could also enter it into 1 cell and have a formula in the next column which adds the dashes using LEFT, MID and/or RIGHT functions.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    The one letter turns the entire thing into a text string, so custom "number formats" don't apply.

    For some reason you can simply enter it as shown (with dashes) in the first cell and reference that cell in the other places that need to show the same value? What would be wrong with entering that way?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    01-14-2009
    Location
    Earth
    MS-Off Ver
    2007
    Posts
    43
    It is always that format:

    Please Login or Register  to view this content.
    and the letters and numbers are always in the same spot. It is a license number for gun dealers.

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582
    #-##-###-##-#A-#####
    The formula answer, lets say you're entering in column B, this would be in Col C. In C2 (and dragged down)
    Please Login or Register  to view this content.
    OR
    Please Login or Register  to view this content.
    Last edited by ChemistB; 01-14-2009 at 03:17 PM.

  6. #6
    Registered User
    Join Date
    01-14-2009
    Location
    Earth
    MS-Off Ver
    2007
    Posts
    43
    That last one works great. The other one combines two cells in a column. That may be because I am using excel 97. Thanks for the help!

    Now I'm going to try figuring out how to apply it to each cell automatically when an empty one has new data entered into it. Now for the fun!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1