TEXT Function: please help

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
0
7/29/2005 11:01:12 AM
excel 39879 articles. 2 followers. Follow

5 Replies
468 Views

Similar Articles

[PageSpeed] 17

=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.

DungFlinger wrote:
> 
> 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

-- 

Dave Peterson
0
petersod (12004)
7/29/2005 12:14:42 PM
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


Dave Peterson wrote:
> =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.
> 
> DungFlinger wrote:
> 
>>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
> 
> 
0
8/2/2005 6:34:11 AM
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.

DungFlinger wrote:
> 
> 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
> 
> Dave Peterson wrote:
> > =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.
> >
> > DungFlinger wrote:
> >
> >>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
> >
> >

-- 

Dave Peterson
0
petersod (12004)
8/2/2005 12:12:33 PM
Does the data analysis add-on provide TEXT() support for hexadecimal?

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

Alex.



Dave Peterson wrote:
> =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.
>
> DungFlinger wrote:
> >
> > 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
> 
> -- 
> 
> Dave Peterson

0
8/2/2005 1:50:06 PM
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

Alex wrote:
> Does the data analysis add-on provide TEXT() support for hexadecimal?
> 
> Maybe you can try to install that and see if it helps.
> 
> Alex.
> 
> 
> 
> Dave Peterson wrote:
> 
>>=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.
>>
>>DungFlinger wrote:
>>
>>>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
>>
>>-- 
>>
>>Dave Peterson
> 
> 
0
8/3/2005 10:24:19 AM
Reply:

Similar Artilces:

Formatting Text in a Forwarded Email Message
Odd problem in Outlook on one of our pc's. We are using Outlook 2002 SP3. When attempting to forward a message, I sometimes change a few of the words in the body of the message. I place the cursor within the word I wish to change. As soon as I type one letter, any letter, the letter shows up two font sizes bigger (from 12 to 14), and with a space on each side of the inserted letter. What seems to make this more puzzling is that when I attempt the same task on our other pc, everything functions as it should; no change in font size and no extra spaces. Any help in eli...

How do I expand the amount of text I can put in a cell?
I am entering text into a cell & can see it in the formula bar up top, but it's giving me the #### in the cell that states it's too big. How can I get it to show my text? Sarah - Put your cursor between the column you want to expand, and the column to the right. When you see a four sided arrow, click and drag it to the right. You may need to also Format, Cell, Alignment and click on Wrap Text. Hope this helps, Carole O "Sarah" wrote: > I am entering text into a cell & can see it in the formula bar up top, but > it's giving me the #### in the cell...

Comparing text strings
I have two columns populated with names and I need to compare them for duplicates. Some duplicate names may contain middle initial or middle names and some don't. So they may not be EXACT. Please give me suggestions on formulas or funcions that would help me accomplish this task. Thanks. On Fri, 27 Nov 2009 11:12:01 -0800, Comparing columns with text <Comparing columns with text@discussions.microsoft.com> wrote: >I have two columns populated with names and I need to compare them for >duplicates. Some duplicate names may contain middle initial or middle names ...

Excel Formula Help #6
Hi Everyone I have six numbers and letters in a cell on a spreadsheet and I am using the formula =RIGHT(F1,1) to obtain the first letter or number. However the formula is not picking the first number or letter in the string in all cases. Some of the cells are left blank. Any help would be much appreciated. Kind Regards Celticshadow > .. Some of the cells are left blank Probably these are trailing white spaces which can be cleaned up using TRIM Try it as: =RIGHT(TRIM(F1),1) High-five? Click YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 S...

Adding functionalities to CRM Mobile
Hi, i want to know if its possible to add functionalities to the CRM Mobile. If i can add "pages" created by me to tha they can be viewed in mobiles. IS SOMEONE THAT CAN HELP ME? regards, pedro airo No, you cannot add new forms. You can fully customize existing ones. Dean "Airo" wrote: > Hi, i want to know if its possible to add functionalities to the CRM Mobile. > If i can add "pages" created by me to tha they can be viewed in mobiles. > > IS SOMEONE THAT CAN HELP ME? > > regards, pedro airo Tnkz for your response. Regards, Pedro Ai...

How to use the countif() function to count items in a table
I have a table contains multple columns of info as shown in the below AcctID ProjectName PMName Phase Status EIG Project 1 Wellie Initiation New MLI Project 2 John Develop Active EIG Project 11 Wellie Qualify Active EIG Project UUU John Design InActive MLI Project XX Carol Deployment Completed MLI Project YYY Carol Design Active I'd like to use the countif() function to complete the following metric such that I know how many projects for each status for each AcctID. EIG ...

help me set up a Event Macros
i am looking to use more than 3 colors in my spreadsheet i can do the conditional formatting for 3 colors but need help to use 5 colors. I am new to this so please tell me how to do this thank -- Message posted from http://www.ExcelForum.com You could use a worksheet_change event: rightclick on the worksheet tab that should have this behavior. Select View code and paste this in the code window. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myColor As Long If Target.Cells.Count > 1 Then Exit Sub If Intersect(Target, Me.Range("a:a")) I...

Change a text box to a combo box
Hi there I have created several forms in my database. I realized that I should have made them combo boxes instead of text boxes. Is there any easy way for me to change them into the format I need? Thank you in advance for your help. Right-click on the text box and select changeTo from the menu. -- Reggie "forest8" <forest8@discussions.microsoft.com> wrote in message news:FD867B89-38CC-4F20-A9AE-C177444DA9DA@microsoft.com...the > Hi there > > I have created several forms in my database. > > I realized that I should have made them combo ...

Pivot Table Text Only, No calculations
:confused: I have a spreadsheet of data, text only. An address list. want to look at the table by city. Is there a way to do this withou calculation? For example, I want to have a city has the page field an look at all the addresses, including columns, within that city. Thanks -- rmm3 ----------------------------------------------------------------------- rmm30's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=835 View this thread: http://www.excelforum.com/showthread.php?threadid=26902 A pivot table is a good tool for summarizing data, but won't help...

Help
Our environment is SBS 2003 server: We have an application that syncronizes a CRM app with Exchange appointments and contacts. This applicaiton has been working for a year now without an issue and all of a sudden, we are getting the following errors: Event Id: 1000 .NET Runtime 2.0 Error -Faulting application tsiexchangesync.exe, version 1.3.0.0, stamp 4a04a7e7, faulting module kernel32.dll, version 5.2.3790.4480, stamp 49c51f0a, debug? 0, fault address 0x0000bef7. Event ID: 1517: Windows saved user Xyz\Administrator registry while an application or service was still us...

MS Query help!
I am in need of using excel and MS Query to intersect 2 tables, one in oracle and one in an excel spread sheet. I have come up with various work arounds, but none seem to be worth it unless I know that this is absolutely impossible in normal circumstances. how can I have 2 data sources for one join query in MS Query. best regards, Alexandre Brisebois ...

Access 2007 date function not working
I had a database in Access 2003 that I imported into 2007 and all works well except the date function in Reports. I have tried to go into reports and repair by adding a date field, but when I try to open the report and error box comes up asks for update parameter value for the date. The currrent date will not display in my reports, just (#name?) is in the date field. You would think trying to put the current date in a report would be easy. -- Thanks, Mark Use this --- =Date() -- KARL DEWEY Build a little - Test a little "Mark K" wrote: > I had a database in Access ...

Help Problems with Outlook tool bar buttons
I'm hoping someone can offer advice on problems that I'm experiencing with Outlook 2003 toolbar buttons. Toolbar buttons usually work fine when I first open Outlook, but after I use a particular button once, it no longer works. For example, I can create a task, then assign the task. the next time I create a task, the assign button no longer works. I have to re-boot outlook to get the button to work again - then it only works once, etc, etc. This is also true of many other (but not all ) Outlook buttons, like "Save and Close", "Recurrence", and others. On...

Displaying Text
Hello, I have an MFC application, where I'm interested in displaying some text for the user in the client area. To do this, I create a device context, and use the .TextOut method to display CStrings. The problem begins when the window is resized, or when a menu that hides the text pops - once the menu is closed, or after the window has been resized, the text (or parts of it) are being erased. How can this be solved? I read that when the window is resized, OnPaint is called, and thus I tried displaying the text from there - hoping to re-display it "after" it has been erased. The...

Linking text to style for auto update
I want to auto update figure/table number in text to that in Figure/Table style numbers Use Insert > Cross Reference to put the numbers into the text; they don't update every time the numbering changes, but only when you tell them to (Ctrl-A, F9) or when you print or switch to Print Preview. On Dec 18, 4:05=A0pm, Taurus <Tau...@discussions.microsoft.com> wrote: > I want to auto update figure/table number in text to that in Figure/Table > style numbers ...

Advanced Filtering
Hello, I've got a worksheet with four columns, one of which is a user name. I would like to take all the user names that contain numbers (and their respective data) and put it into a separate worksheet. So far, I haven't had any luck doing this with any combination of wildcard operators. The format of the user names are two or three letters and then 4 numbers, or they are entirely text. So the list has data like abc1234, ab1234 and abcdef. I want all the abc1234 and ab1234 entries in a separate list. Any suggestions? Thanks in advance. Maybe you could use a helper column and filt...

Outlook 2003 Problem-Need immediate help
Hi, I have been using Outlook 2003 and XP for many years. For last two days I am facing huge problem with Outlook as it does not download new message and when I click send/receive the CPU usage surges to 100%. I tried safe mode etc but it does not work. I shall highly appreciate immediate advice to solve this problem. Thanx Do you have a virus scanner installed which integrates itself with Outlook? Uninstall this integration part of your virus scanner and try again; you'd still be sufficiently protected by your on-access scanner part of the virus scanner. For more details s...

Chart turns gray.. can anyone help
Hi, I am working with a large file.. but not as big as others. This one has a few charts. When I add another it starts acting weird... turning totally gray unless I click on it...? its it the fact that its not embedded or not in front or in back of the worksheet? Thanks for any helpful ideas on correcting this. Go to Options on the Tools menu, click on the View tab, and under Objects, select View All. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Steve Kay wrote: > Hi, I am working with a large file.. but ...

ROWS Function
In Office Help, there is an example of the FOWS Function. In B2, the fumula is : "=ROWS(C1:E4)" I believe this one is easy to understand. However, another fumula in cell A3 said: =ROWS({1,2,3;4,5,6}) The description about it is "Number of rows in the array constant". The result is "2". Could you please explain it in a easy way for me to understand? Thanks. the example mentions 2 arrays. the rows function is supposed to retur the number of rows in any given array. first: "=ROWS(C1:E4)" C1 D1 E1 C2 D2 E2 C3 D3 E3 C4 D4 E4 this is a 4 x 3 matrix, 4...

Copying Formula Text
Is it possible to copy and paste just the text of a formula from on cell to another without going into the formula text window and usin Ctrl+c. Can this be done from multiple cells to multple cells. thanks -- jp ----------------------------------------------------------------------- jpx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=101 View this thread: http://www.excelforum.com/showthread.php?threadid=37716 edit/copy select new location edit/paste special, select Values. OR... Right-click the BORDER of the selection and drag to the new location. When you ...

In POwer POint 2003 can I gradually add text in the same slide?
I want to gradually add text in my presentation within the same slide. For ex: The beginning of an equation and click the mouse to complete the equation as I explain it. Read the instruction here: http://pptfaq.com/FAQ00581.htm -- James Bart: "According to creationism, there were no cavemen." Homer: "Well good riddance, their drawings sucked and they all looked like hippies!" "PP HELP" <PP HELP@discussions.microsoft.com> wrote in message news:6190E093-00D4-492A-AD1A-3F589B809E5D@microsoft.com... > I want to gradually add text in ...

Text wrapping #4
Hi All I am just trying to create a document using publisher 2003. I have some pictures on the page and have text boxes containing the text I want. The problem is that it seems to be putting in the - in the text when it spreads it on to the next line. How can I stop it doing this and move the whole word on to the next line. Many thank Glenn Tools, language, hyphenation, clear the box. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Glenn Clark" <glenn.(NOSPAMclark@btinternet(NOSPAM).com> wrote in message ne...

inserting text form field in a text box
I'm designing a form that has several text boxes I want to be able to insert a text form field that I can type in different names in when needed, while keeping the rest of the text constant. Every time I go to insert form field from Legacy it is not available. Anybody have a solution. Your subject line has the missing clue. There is no way to insert a legacy form field (text or any other kind) in a text box, footnote, endnote, or header/footer. You can remove the text box and put the field into ordinary text, or you can use a text content control in a text box or elsewhe...

How to preserve Rich Text Format on replies??
Our Exchange server is version 5.5 still. I want to set up the Exchange server such that if a domain user replies to a message that arrived in Rich Text format from another Outlook user on the Internet the reply shall also be in rich text. As it is now our users receive the message in rich text. Then they reply and edit the reply with rich text so they can color code certain parts of it. And they also copy the reply to a user in the same company, i.e. on the same Exchange server. But when the reply is sent out to the original sender he receives the message in plaintext, whereas the internal...

How to set form caption / text?
I use MFC Visual C++ 4.2. I am developing an application with main window and a few childs window. How to set each child window text / caption everytime I call DoModal? If I set "Entry Form" then the window I call will displayed with "Entry Form" caption. Thank you. You can use SetWindowText to set it. For a dialog, you should do it any time after its created(IOW, not in constructor). OnInitDialog and after is fine. -- Ajay "Landon" <Landon@discussions.microsoft.com> wrote in message news:F18C3988-E5F2-413E-8CCF-78143066DB29@microsoft.com... >I ...