Formula Help

  • Thread starter Thread starter LavaDude
  • Start date Start date
L

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
 
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
 
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&";}")
 
I like the second one Bob... but the ":" (colons) aren't showing up ...
Any ideas?

Thanks so much!

LavaDude
 
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)?
 
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...
 
No, I thought you were playing with hardware addresses, all numeric, The
formats won't work on strings like that I am afraid.
 
Back
Top