Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Computing > Computer Support > MS Excell formatting question

Reply
Thread Tools

MS Excell formatting question

 
 
Jack B. Pollack
Guest
Posts: n/a
 
      09-19-2003
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


 
Reply With Quote
 
 
 
 
bb3
Guest
Posts: n/a
 
      09-19-2003

"Jack B. Pollack" <(E-Mail Removed)> 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?


 
Reply With Quote
 
 
 
 
Jack B. Pollack
Guest
Posts: n/a
 
      09-19-2003
Sorry, I forgot to mention that I want the hyphens inserted automatically
after every two characters.

Thanks



"bb3" <(E-Mail Removed)> wrote in message
news:5GKab.145588$(E-Mail Removed)...
>
> "Jack B. Pollack" <(E-Mail Removed)> 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?
>
>



 
Reply With Quote
 
SgtMinor
Guest
Posts: n/a
 
      09-19-2003
"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?
 
Reply With Quote
 
Stevo
Guest
Posts: n/a
 
      09-19-2003
Jack B. Pollack wrote:
> Sorry, I forgot to mention that I want the hyphens inserted
> automatically after every two characters.
>
> Thanks
>
>
>
> "bb3" <(E-Mail Removed)> wrote in message
> news:5GKab.145588$(E-Mail Removed)...
>>
>> "Jack B. Pollack" <(E-Mail Removed)> 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)


 
Reply With Quote
 
why?
Guest
Posts: n/a
 
      09-19-2003

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
 
Reply With Quote
 
Jack B. Pollack
Guest
Posts: n/a
 
      09-19-2003
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:(E-Mail Removed)...
>
> 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



 
Reply With Quote
 
why?
Guest
Posts: n/a
 
      09-19-2003

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:(E-Mail Removed).. .
>>
>> 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
 
Reply With Quote
 
Gary Danaher
Guest
Posts: n/a
 
      09-20-2003
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" <(E-Mail Removed)> wrote in message
> news:5GKab.145588$(E-Mail Removed)...
>
>>"Jack B. Pollack" <(E-Mail Removed)> 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?
>>
>>

>
>
>


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
An excell question Robin Halligan NZ Computing 2 06-20-2007 10:08 AM
Datafeed to Excell Steve Peterson ASP .Net 1 02-01-2006 03:30 PM
asp.net and excell.. file share problems Robert Halstead ASP .Net 2 10-28-2003 02:14 AM
Excell 2002 AKPilot Computer Support 3 07-25-2003 11:53 AM
Formatting Excell doc from within MFC app magic C++ 1 07-06-2003 01:17 PM



Advertisments