Formula Help

[LavaDude]

A friend of mine asked me if the following formula can be shortened?

=IF(D2="","",CONCATENATE("host ",L2,IF(C2="","","."),IF(OR(B2="",B2>
30,),"",CONCATENATE(B2,"-")),C2," { hardware ethernet ",(CONCATENATE(LEFT
(D2,2),":",RIGHT(LEFT(D2,4),2),":",RIGHT(LEFT(D2,6),2),":",RIGHT(LEFT
(D2,8),2),":",RIGHT(LEFT(D2,10),2),":",RIGHT(D2,2),"; fixed-address ",M2,";
}"))))

FYI: The cell D2 contains: 00E06F734032

Is there a way? Thanks in advance...

LavaDude

[Anne Troy]

You can start by getting rid of the word "concatenate".

=if(D2="","","host"&L2,IF....
(You don't need "concatenate". Usually, you can just use the &.
*******************
~Anne Troy

www.OfficeArticles.com


"LavaDude" <[email protected]> wrote in message
news:[email protected]...
> A friend of mine asked me if the following formula can be shortened?
>
> =IF(D2="","",CONCATENATE("host ",L2,IF(C2="","","."),IF(OR(B2="",B2>
> 30,),"",CONCATENATE(B2,"-")),C2," { hardware ethernet ",(CONCATENATE(LEFT
> (D2,2),":",RIGHT(LEFT(D2,4),2),":",RIGHT(LEFT(D2,6),2),":",RIGHT(LEFT
> (D2,8),2),":",RIGHT(LEFT(D2,10),2),":",RIGHT(D2,2),"; fixed-address
",M2,";
> }"))))
>
> FYI: The cell D2 contains: 00E06F734032
>
> Is there a way? Thanks in advance...
>
> LavaDude

[Bob Phillips]

Just a play

=IF(D2="","","host "&L2&IF(C2="","",".")&IF(OR(B2="",B2>30,),"",B2&"-")&C2&"
{ hardware ethernet
"&LEFT(D2,2)&":"&MID(D2,3,2)&":"&MID(D2,5,2)&":"&MID(D2,7,2)&":"&MID(D2,9,2)
&":"&RIGHT(D2,2)&"; fixed-address "&M2&";}")

or maybe

=IF(D2="","","host "&L2&IF(C2="","",".")&IF(OR(B2="",B2>30,),"",B2&"-")&C2&"
{ hardware ethernet "&TEXT(D2,"00\:00\:00\:00\:00\:00")&"; fixed-address
"&M2&";}")

--
HTH

Bob Phillips

"LavaDude" <[email protected]> wrote in message
news:[email protected]...
> A friend of mine asked me if the following formula can be shortened?
>
> =IF(D2="","",CONCATENATE("host ",L2,IF(C2="","","."),IF(OR(B2="",B2>
> 30,),"",CONCATENATE(B2,"-")),C2," { hardware ethernet ",(CONCATENATE(LEFT
> (D2,2),":",RIGHT(LEFT(D2,4),2),":",RIGHT(LEFT(D2,6),2),":",RIGHT(LEFT
> (D2,8),2),":",RIGHT(LEFT(D2,10),2),":",RIGHT(D2,2),"; fixed-address
",M2,";
> }"))))
>
> FYI: The cell D2 contains: 00E06F734032
>
> Is there a way? Thanks in advance...
>
> LavaDude

[LavaDude]

I like the second one Bob... but the ":" (colons) aren't showing up ...
Any ideas?

Thanks so much!

LavaDude

"Bob Phillips" <[email protected]> wrote in
news:[email protected]:

> Just a play
>
> =IF(D2="","","host
> "&L2&IF(C2="","",".")&IF(OR(B2="",B2>30,),"",B2&"-")&C2&" { hardware
> ethernet
> "&LEFT(D2,2)&":"&MID(D2,3,2)&":"&MID(D2,5,2)&":"&MID(D2,7,2)&":"&MID(D2
> ,9,2) &":"&RIGHT(D2,2)&"; fixed-address "&M2&";}")
>
> or maybe
>
> =IF(D2="","","host
> "&L2&IF(C2="","",".")&IF(OR(B2="",B2>30,),"",B2&"-")&C2&" { hardware
> ethernet "&TEXT(D2,"00\:00\:00\:00\:00\:00")&"; fixed-address
> "&M2&";}")
>

[Bob Phillips]

They did in my test dude. What value did you have in B2, C2 and M2?

Can you post a workbook somewhere that I can look at (not the NG, not
approved)?

--
HTH

Bob Phillips

"LavaDude" <[email protected]> wrote in message
news:[email protected]...
> I like the second one Bob... but the ":" (colons) aren't showing up ...
> Any ideas?
>
> Thanks so much!
>
> LavaDude
>
> "Bob Phillips" <[email protected]> wrote in
> news:[email protected]:
>
> > Just a play
> >
> > =IF(D2="","","host
> > "&L2&IF(C2="","",".")&IF(OR(B2="",B2>30,),"",B2&"-")&C2&" { hardware
> > ethernet
> > "&LEFT(D2,2)&":"&MID(D2,3,2)&":"&MID(D2,5,2)&":"&MID(D2,7,2)&":"&MID(D2
> > ,9,2) &":"&RIGHT(D2,2)&"; fixed-address "&M2&";}")
> >
> > or maybe
> >
> > =IF(D2="","","host
> > "&L2&IF(C2="","",".")&IF(OR(B2="",B2>30,),"",B2&"-")&C2&" { hardware
> > ethernet "&TEXT(D2,"00\:00\:00\:00\:00\:00")&"; fixed-address
> > "&M2&";}")
> >
>

[LavaDude]

The portion of the formula that's not working is:

TEXT(D2,"00\:00\:00\:00\:00\:00")

On my spreadsheet, it inserts the colons if the contents of D2 is a
number, but because D2 contains letters too (i.e. 00E06F734032), then the
above formula does not insert the colons...

Do I need to download a plug-in for excel? I'm using Excel 2000 (SP-3)

If you're willing, can I e-mail you the file? I'll have to ask if it's
okay with my friend that I do this...

Thanks again!

LavaDude...


"Bob Phillips" <[email protected]> wrote in news:ekB4ZMGlFHA.3336
@tk2msftngp13.phx.gbl:

> They did in my test dude. What value did you have in B2, C2 and M2?
>
> Can you post a workbook somewhere that I can look at (not the NG, not
> approved)?
>

[Bob Phillips]

No, I thought you were playing with hardware addresses, all numeric, The
formats won't work on strings like that I am afraid.

--
HTH

Bob Phillips

"LavaDude" <[email protected]> wrote in message
news:[email protected]...
> The portion of the formula that's not working is:
>
> TEXT(D2,"00\:00\:00\:00\:00\:00")
>
> On my spreadsheet, it inserts the colons if the contents of D2 is a
> number, but because D2 contains letters too (i.e. 00E06F734032), then the
> above formula does not insert the colons...
>
> Do I need to download a plug-in for excel? I'm using Excel 2000 (SP-3)
>
> If you're willing, can I e-mail you the file? I'll have to ask if it's
> okay with my friend that I do this...
>
> Thanks again!
>
> LavaDude...
>
>
> "Bob Phillips" <[email protected]> wrote in news:ekB4ZMGlFHA.3336
> @tk2msftngp13.phx.gbl:
>
> > They did in my test dude. What value did you have in B2, C2 and M2?
> >
> > Can you post a workbook somewhere that I can look at (not the NG, not
> > approved)?
> >
>