TEXT Function: please help

  • Thread starter Thread starter DungFlinger
  • Start date Start date
D

DungFlinger

I have a sheet that keeps track of MAC to IP adresses. I've created a
formula that will output the required format for DHCP server import.

The formula was quite long and it was suggested to use the TEXT function .

Thanks to Bob Phillips and my friend LavaDude my rather legthy formula
has been reduced to the folowing, but it doesnt work correctly.

=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&";}")

However the TEXT function seems to work perfectly but only on numbers
(or "value" according to help) The Alphabets causes the MAC address to
be in it's original format: AABBCC001122. The format that I need it to
be is: AA:BB:CC:00:11:22 I have accomplished this with the LEFT,RIGHT
and MID functions. It's so close there must be a way to use the TEXT
func... any help would be greatly appreciated.

df
 
=text() works with numbers--not text.

Maybe you could use a combination of =mid() and =text()

If they're always:
aabbcc###### format

=MID(d2,1,2)&":"&MID(d2,3,2)&":"&MID(d2,5,2)&TEXT(--MID(d2,7,6),"\:00\:00\:00")

(Plop that expression into your formula (and remove the existing &Text(...)
portion.

The -- converts the last =mid() function from text to number.
 
Thanks for the suggestion, but I am already using the MID function. MAC
addresses are somewhat random, especially the last 6 digits. Basically
a MAC address consists of 12 hexadecimals. (0-9, a-f) The first six
define a manufacturer and the last six can be thought of as a serial number.

thank tho

df
 
Since that value is text in the cell, I think you'll either need the =mid()
functions or some code to format the cell the way you want.
 
Does the data analysis add-on provide TEXT() support for hexadecimal?

Maybe you can try to install that and see if it helps.

Alex.
 
I was wondering about some kind of add-on.. If I did get the add-on and
it worked.. How would it appear to someone if I was to send them the
file but they don't have the add-on? I guess I'm wondering if the
add-on moves with the file? I need compatibilty, because the file gets
sent to several departments.

thanks for the suggestion,

df
Does the data analysis add-on provide TEXT() support for hexadecimal?

Maybe you can try to install that and see if it helps.

Alex.
Click to expand...
 
Back
Top