MS Excell formatting question

Discussion in 'Computer Support' started by Jack B. Pollack, Sep 19, 2003.

  1. I am trying to format some characters (Network card MAC addresses) in MS
    Excel. They are groups of NUMBERS or LETTERS that are separated by dashes
    (and I need leading 0 to display).

    eg 00-00-1A-47-33-22.

    How do I setup the format for the cell? I have tried 00-00-00-00-00-00 this
    almost works but fails with LETTERS.

    Thanks
     
    Jack B. Pollack, Sep 19, 2003
    #1
    1. Advertising

  2. Jack B. Pollack

    bb3 Guest

    "Jack B. Pollack" <> wrote in message
    news:1064005248.220540@news2...
    > I am trying to format some characters (Network card MAC addresses) in MS
    > Excel. They are groups of NUMBERS or LETTERS that are separated by dashes
    > (and I need leading 0 to display).
    >
    > eg 00-00-1A-47-33-22.
    >
    > How do I setup the format for the cell? I have tried 00-00-00-00-00-00

    this
    > almost works but fails with LETTERS.
    >
    > Thanks
    >
    >

    Try formatting the cells as Text?
     
    bb3, Sep 19, 2003
    #2
    1. Advertising

  3. Sorry, I forgot to mention that I want the hyphens inserted automatically
    after every two characters.

    Thanks



    "bb3" <> wrote in message
    news:5GKab.145588$...
    >
    > "Jack B. Pollack" <> wrote in message
    > news:1064005248.220540@news2...
    > > I am trying to format some characters (Network card MAC addresses) in MS
    > > Excel. They are groups of NUMBERS or LETTERS that are separated by

    dashes
    > > (and I need leading 0 to display).
    > >
    > > eg 00-00-1A-47-33-22.
    > >
    > > How do I setup the format for the cell? I have tried 00-00-00-00-00-00

    > this
    > > almost works but fails with LETTERS.
    > >
    > > Thanks
    > >
    > >

    > Try formatting the cells as Text?
    >
    >
     
    Jack B. Pollack, Sep 19, 2003
    #3
  4. Jack B. Pollack

    SgtMinor Guest

    "Jack B. Pollack" wrote:
    >
    > I am trying to format some characters (Network card MAC addresses) in MS
    > Excel. They are groups of NUMBERS or LETTERS that are separated by dashes
    > (and I need leading 0 to display).
    >
    > eg 00-00-1A-47-33-22.
    >
    > How do I setup the format for the cell? I have tried 00-00-00-00-00-00 this
    > almost works but fails with LETTERS.
    >
    > Thanks


    What was the nature of the failure? What outcome resulted? I tried
    several variations of the string in your example in Excel 2000 and they
    all showed up exactly as typed, leading oughts and all, without needing
    any cell formatting. Or is this a case were you are trying to merge
    data into a cell?
     
    SgtMinor, Sep 19, 2003
    #4
  5. Jack B. Pollack

    Stevo Guest

    Jack B. Pollack wrote:
    > Sorry, I forgot to mention that I want the hyphens inserted
    > automatically after every two characters.
    >
    > Thanks
    >
    >
    >
    > "bb3" <> wrote in message
    > news:5GKab.145588$...
    >>
    >> "Jack B. Pollack" <> wrote in message
    >> news:1064005248.220540@news2...
    >>> I am trying to format some characters (Network card MAC addresses)
    >>> in MS Excel. They are groups of NUMBERS or LETTERS that are
    >>> separated by dashes (and I need leading 0 to display).
    >>>
    >>> eg 00-00-1A-47-33-22.
    >>>
    >>> How do I setup the format for the cell? I have tried
    >>> 00-00-00-00-00-00 this almost works but fails with LETTERS.
    >>>
    >>> Thanks
    >>>
    >>>

    >> Try formatting the cells as Text?


    Dont know that that is possible with standard formatting commands for cells
    using placeholders..

    but you could do it with a formula..

    with the raw data 00001A432F6A in cell C2 the formula in cell E2 would be :

    =LEFT(C2,2)&"-"&MID(C2,3,2)&"-"&MID(C2,5,2)&"-"&MID(C2,7,2)&"-"&MID(C2,9,2)&
    "-"&RIGHT(C2,2)

    giving a result of 00-00-1A-43-2F-6A as text, in E3.


    --
    Stevo
    (pull the PIN to reply by e-mail)
     
    Stevo, Sep 19, 2003
    #5
  6. Jack B. Pollack

    why? Guest

    On Fri, 19 Sep 2003 17:00:28 -0400, Jack B. Pollack wrote:

    >I am trying to format some characters (Network card MAC addresses) in MS
    >Excel. They are groups of NUMBERS or LETTERS that are separated by dashes
    >(and I need leading 0 to display).
    >


    AMD
    >eg 00-00-1A-47-33-22.


    So that's what you want, what are you starting off with? You missed that
    bit.

    You have a leading 0 anywhere, your example should have been -
    0-0-1A-47-33-22 or something different?

    >How do I setup the format for the cell? I have tried 00-00-00-00-00-00 this
    >almost works but fails with LETTERS.


    Argh it's the LETTERS in loud caps.

    What's generating the MACs before they get to Excel. Most of the time I
    use perl scripts and utils that format these in Excel no problems.

    Try changing the Excel (import option) field General/Number/Text.


    Me
     
    why?, Sep 19, 2003
    #6
  7. I am just manually entering data (not importing it). I could manually enter
    the dashes, but thought that it would be nicer if I could just enter the
    data as it is given to me (in one long string) and have Excel format it for
    me.


    "why?" <fgrirp*sgc@VAINY!Qznq.fpvragvfg.pbz> wrote in message
    news:...
    >
    > On Fri, 19 Sep 2003 17:00:28 -0400, Jack B. Pollack wrote:
    >
    > >I am trying to format some characters (Network card MAC addresses) in MS
    > >Excel. They are groups of NUMBERS or LETTERS that are separated by dashes
    > >(and I need leading 0 to display).
    > >

    >
    > AMD
    > >eg 00-00-1A-47-33-22.

    >
    > So that's what you want, what are you starting off with? You missed that
    > bit.
    >
    > You have a leading 0 anywhere, your example should have been -
    > 0-0-1A-47-33-22 or something different?
    >
    > >How do I setup the format for the cell? I have tried 00-00-00-00-00-00

    this
    > >almost works but fails with LETTERS.

    >
    > Argh it's the LETTERS in loud caps.
    >
    > What's generating the MACs before they get to Excel. Most of the time I
    > use perl scripts and utils that format these in Excel no problems.
    >
    > Try changing the Excel (import option) field General/Number/Text.
    >
    >
    > Me
     
    Jack B. Pollack, Sep 19, 2003
    #7
  8. Jack B. Pollack

    why? Guest

    On Fri, 19 Sep 2003 18:44:30 -0400, Jack B. Pollack wrote:

    >I am just manually entering data (not importing it). I could manually enter


    Grim, how much? Don't you have anything to suck the data in it's far
    easier.

    >the dashes, but thought that it would be nicer if I could just enter the
    >data as it is given to me (in one long string) and have Excel format it for
    >me.


    Well in that case, a simple Macro using VBA.

    Create a text input box.
    Parse the string entered to check length / leading 0.
    Add the '- '
    Then copy it to the spreadsheet.

    I have done very little VBA myself, so a visit to www.google.com for

    VBA tutorial input box example
    VBA tutorial string example

    However it's fairly simple

    1 example is
    http://www.mindspring.com/~tflynn/excelvba3.html#Input

    To see this working

    1. Open Excel
    2. Tools / Macro / Macros
    3. Enter a name like - CalcPay
    4. Click Create
    That should have launched the VBA editor
    Main Window Book1 - Module 1 (Code)
    looks like

    Sub CalcPay()

    End Sub


    Replace those lines by the lines below.


    Sub CalcPay()
    On Error GoTo HandleError
    Dim hours
    Dim hourlyPay
    Dim payPerWeek
    hours = InputBox("Please enter number of hours worked", "Hours Worked")
    hourlyPay = InputBox("Please enter hourly pay", "Pay Rate")
    payPerWeek = CCur(hours * hourlyPay)
    MsgBox "Pay is: " & Format(payPerWeek, "$##,##0.00"), , "Total Pay"
    HandleError:
    End Sub



    5. Click floppy (save)
    6. Click the blue > to run
    or Run/Run Sub/UserForm from the menu
    or press F5 , all of these run the macro.

    >
    >"why?" <fgrirp*sgc@VAINY!Qznq.fpvragvfg.pbz> wrote in message
    >news:...
    >>
    >> On Fri, 19 Sep 2003 17:00:28 -0400, Jack B. Pollack wrote:
    >>
    >> >I am trying to format some characters (Network card MAC addresses) in MS
    >> >Excel. They are groups of NUMBERS or LETTERS that are separated by dashes
    >> >(and I need leading 0 to display).
    >> >

    >>
    >> AMD
    >> >eg 00-00-1A-47-33-22.

    >>
    >> So that's what you want, what are you starting off with? You missed that
    >> bit.
    >>

    <snip>

    Me
     
    why?, Sep 20, 2003
    #8
  9. Jack B. Pollack

    Gary Danaher Guest

    If this is a one shot deal, format an Excel spreadsheet totally as text,
    insert the blocks of letters/numbers into each cell, allowing for the
    tabbed space between each group to become a dash. Copy the whole thing
    to Word, Go to change and choose to change the tab between each block of
    characters to a hyphen. Don't think there is any way for Excel to auto
    insert a hyphen anywere as you'd like.

    Jack B. Pollack wrote:

    > Sorry, I forgot to mention that I want the hyphens inserted automatically
    > after every two characters.
    >
    > Thanks
    >
    >
    >
    > "bb3" <> wrote in message
    > news:5GKab.145588$...
    >
    >>"Jack B. Pollack" <> wrote in message
    >>news:1064005248.220540@news2...
    >>
    >>>I am trying to format some characters (Network card MAC addresses) in MS
    >>>Excel. They are groups of NUMBERS or LETTERS that are separated by

    >
    > dashes
    >
    >>>(and I need leading 0 to display).
    >>>
    >>>eg 00-00-1A-47-33-22.
    >>>
    >>>How do I setup the format for the cell? I have tried 00-00-00-00-00-00

    >>
    >>this
    >>
    >>>almost works but fails with LETTERS.
    >>>
    >>>Thanks
    >>>
    >>>

    >>
    >>Try formatting the cells as Text?
    >>
    >>

    >
    >
    >
     
    Gary Danaher, Sep 20, 2003
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. AKPilot

    Excell 2002

    AKPilot, Jul 24, 2003, in forum: Computer Support
    Replies:
    3
    Views:
    483
    Simeon
    Jul 25, 2003
  2. Bibble Babble

    Excell Cell references are now

    Bibble Babble, Nov 13, 2003, in forum: Computer Support
    Replies:
    7
    Views:
    911
    Bibble Babble
    Nov 15, 2003
  3. Tommy

    EXCELL SPREED SHEET???

    Tommy, Dec 6, 2003, in forum: Computer Support
    Replies:
    5
    Views:
    10,266
    Boomer
    Dec 7, 2003
  4. wolfie

    lost key 4 excell and word

    wolfie, Feb 13, 2005, in forum: Computer Support
    Replies:
    1
    Views:
    399
    Toolman Tim
    Feb 13, 2005
  5. Robin Halligan

    An excell question

    Robin Halligan, Jun 20, 2007, in forum: NZ Computing
    Replies:
    2
    Views:
    322
    Robin Halligan
    Jun 20, 2007
Loading...

Share This Page