Formula Help #10

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
0
mikioi (36)
7/26/2005 11:28:23 PM
excel 39879 articles. 2 followers. Follow

6 Replies
773 Views

Similar Articles

[PageSpeed] 2

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" <mikioi@TAKEOUTgte.net> wrote in message
news:Xns969F891042BCCmikioigtenet@216.168.3.44...
> 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


0
ng1 (1444)
7/26/2005 11:50:02 PM
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" <mikioi@TAKEOUTgte.net> wrote in message
news:Xns969F891042BCCmikioigtenet@216.168.3.44...
> 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


0
phillips1 (803)
7/27/2005 8:46:12 AM
I like the second one Bob... but the ":" (colons) aren't showing up ... 
Any ideas?

Thanks so much!  

LavaDude

"Bob Phillips" <phillips@tiscali.co.uk> wrote in
news:eNelqeokFHA.576@TK2MSFTNGP15.phx.gbl: 

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

0
mikioi (36)
7/29/2005 3:33:53 AM
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" <mikioi@TAKEOUTgte.net> wrote in message
news:Xns96A1B2AEAD5AEmikioigtenet@216.168.3.44...
> I like the second one Bob... but the ":" (colons) aren't showing up ...
> Any ideas?
>
> Thanks so much!
>
> LavaDude
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in
> news:eNelqeokFHA.576@TK2MSFTNGP15.phx.gbl:
>
> > 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&";}")
> >
>


0
phillips1 (803)
7/29/2005 5:29:24 PM
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" <phillips@tiscali.co.uk> 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)?
> 

0
mikioi (36)
7/29/2005 9:56:25 PM
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" <mikioi@TAKEOUTgte.net> wrote in message
news:Xns96A27976C5E21mikioigtenet@216.168.3.44...
> 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" <phillips@tiscali.co.uk> 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)?
> >
>


0
phillips1 (803)
7/30/2005 1:24:18 PM
Reply:

Similar Artilces:

Macro Help 11-24-09
I have one workbook of data (1 tab) that has data for 20 different Sales Reps (different names). I need to copy all data for "Rep A" into a separate worksheet, and same for "Rep B" and so on. At the end I would have 1 tab for all data and 20 tabs with the data for each rep. Basically, I need to copy and paste each rep data into a new worksheet within the same workbook but didn't want to do it manually. I hope this makes sense. See Ron de Bruin's site for code. http://www.rondebruin.nl/copy5.htm Also check out his easyfilter add-in. http://www.ro...

Excel 97 VBA Help File
In the MS Excel Visual Basic Reference help file contents page, I click on Functions and it only offers me functions beginning with the letter S. So, I have a list of Solver and SQL functions. But what about all the other functions in VBA, for example for doing arithmetic and manipulating dates and strings? Why don't they show up? Are they left out because those functions are all part of Visual Basic generally, and the Excel VBA help file is specific to the _extra_ functions in Excel VBA? It's the only explanation I can think of. Am I right, or have I got a corrupted help file (vbaxl...

help need with VC 6.0 IDE and mfc
Hello, First let me explain the scenario where i m using this requirement. We are Using CustomAppWizard and designing a wizard .One of the wizard pages will Insert Composite controls as many as the user wants . 1.So i should be able to dynamically insert ATL controls without using Insert Control Dailog. 2. can any one tell me how to dynamically create Template file in TEMPLATE folder of resource view . 3. I want to include many files created by templet files and add them to build by editing newproj.inf Is it possible to do this. 4.I would even like to know if i have 2 ifles in my C drive h...

Help, I cannot Save!
I created a document and locked the worksheet to protect the formulars before creating a template for the document. But now when I open th document and insert a new sheet using the template I created, th document will refuse to save. Once I click on save, office assistant will say "doc not saved". Wha could I have done wrong? PLease help. computerfinema -- computerfinema ----------------------------------------------------------------------- computerfineman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3716 View this thread: http://www.excelforum.c...

Help Required
Hi, Whenever I open Outlook 2003, I am getting a dialog box which displays the following message: Microsoft Office Outlook has encountered a problem and needs to close. We are sorry for the inconvenience When I click Debug it displays a message box with the following error message "The instruction at "0x3007e993" referenced memory at "0x0000000:. The memory could not be read" When I click No it Visual Studio JIT debugger pops up. I uninstalled and installed several times but still the problem persists. Is there any regsitry entry that I've to modify/delete? ...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Windows Server 2008 R2 04-09-10
Windows Server 2008 R2 and Windows 7 share the same code? how is that possible when Windows 7 has both 32 bit and 64 bit versions and windows server 2008 r2 is only 64 bit Hello Charle, As Microsoft is going to use only 64bit versions for servers they don't built the 32bit version. Sharing the same code doesn't mean that the server OS use exaclty the same files, there are a lot more and different ones. But the basic code is the same. Best regards Meinolf Weber Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights. ...

HELP Recovering addresses and email from Outlook 2003
I had some serious driver issues that required re-installing XP from disc. I did use the backup option and have a backup of all the old data. And of course had to reinstall Office 2003. Will third party software restore my old email and addresses or am I out of luck?? Thanks for the help texraid wrote: > I had some serious driver issues that required re-installing XP from > disc. I did use the backup option and have a backup of all the old > data. And of course had to reinstall Office 2003. > > Will third party software restore my old email and addresses or am I > out of lu...

Need Help with Deleting Empty Paragraphs in Word 2003
I have written the code below to delete all empty paragraphs at the end of a document and then place the cursor at the end of the last paragraph. It works fine as a stand alone sub in a new doc, but fails inside the real document that contains other code that manipulates several documents. The failure is that it will delete the last empty para, but then gets stuck looping inside the While...Wend because subsequent .Delete are not happening. So, the question is why would this work in one document, but then fail in another? n = 0 ...

Extending formulas
Subject: Extending formulas Hi, For my application that uses Excel for calculations. I need to be able to extend the forula base of Excell with complex scientifc functions. Is there a way to add new functions to the Excel function base? Thanks Spx. MS has provided Visual Basic for Applications (VBA) to customize Excel with new functions, commands, forms, menus, etc. Tools|Macro|Visual Basic Editor From the VBA editor Insert Module Then write your functions in VBA. Details of writting functions in VBA is a very big topic, http://www.fontstuff.com/vba/vbatut01.htm may help y...

Invoice Numbers 10-27-07
We produce reports that are invoices.. The reports are really a group of compined reports if this matters... When we print the reports I would like to have printed consecutive invoice numbers. If possible I would like to have the number apprear as AS-00001, AS-00002 ect.. I am not really interested in storing the invoice numbers I just need them on the printed invoice as it is made of of groups of various data that is stored... Thank In Advance for you help. Bob If you just want a consequetive numbering on the report, all with an AS- prefix, see: Numbering Entries in a Report o...

Help me identify my missing permission (Cannot open public folder) -2147217843 (Maybe Authentication Fails?)
The following snippet of code throws an error number -2147217843. When I googled this error code, I see many references to authentication failed. I am assuming my problem is some kind of permission related problem on the "MyNewFolder" public folder. -- start code --- Dim objFolder As New CDO.Folder Dim f As ADODB.Field 'sURL is like: file://./backofficestorage/mydomain.com/Public Folders/MyNewFolder/ objFolder.DataSource.Open sURL, , adModeReadWrite, adFailIfNotExists --- end code -- I have code that runs before this that actually creates the "MyNewFolder" publ...

GP 10 AP Reconciliation Statement
Hi All, I have one query about the reconcile feature that is available in GP 10 to reconcile the AP and AR to GL. It has come to my notice when i take a recon statement for AP it does not match with the AP historical aged trail balance after taking into consideration the unmatched and potentially matched transactions. I have taken the AP smartlist for the given period and compared it with the transaction being displayed in the recon statement. What has come to my notice is the recon statement is not taking few transaction like invoice or payment for some reason. I faced this issue with almo...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula ($L$1). Currently the formul is:=VLOOKUP($A$1,$AD$7:$AG$44,IF($L$1="January",2,IF($L$1="February",2,IF($L$1="March",2,IF($L$1="April",2,IF($L$1="MAY",4,IF($L$1="June",3,IF($L$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Help with Registration
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi, I've tried to register my copy of Office for Mac through the Mactopia page. I log in successfully, but then it just keeps on loading and doesn't refresh or change. Any advice? On 6/16/09 6:35 PM, in article 59b76b64.-1@webcrossing.caR9absDaxw, "theconfuzed1@officeformac.com" <theconfuzed1@officeformac.com> wrote: > I've tried to register my copy of Office for Mac through the Mactopia page. I > log in successfully, but then it just keeps on loading and doesn't refresh or > ...

Help about numeric type field. Thanks.
I created a SQL Server 2005 CE DB ( .sdf, version 3.0) with vs2005. And I created a table which has 2 fields: fld1 - int, fld2 - numeric(38,25). But I encounted an error messagebox when I tried to insert a record (4005,9000000). The msgbox said Conversion overflows. The setting for my numeric fld2 is (precision=38,scale=25). So why occur error when to insert 9000000? Thanks in advance. ...

HELP! Outlook POP3 problem(s)
Hello. I am so lost. I have a few e-mail accounts set up on my computer which retrieves my mail from a couple of different providers and deposits the mail into my Outlook Inbox. Up until yesterday, my mail always has worked fine. For some strange reason, my Outlook is now (Again) retrieving my messages from all of my accounts I had set up, which are all duplicates of my messages. There is now nearly 4,000 duplicate messages in my folders. I can't seem to stop the download of these already retrieved messages. To top things off, a couple of my email account login windows keep p...

Spam Filtering HELP
I recently started a new job, and discovered after day one, that I had inharited a spam mess. Now the previous admin ad installed a Symantic Spam Server Prox which in my opinion, was a complete waste of money as it does not allow for blocking IP addresses. Now here is the question; I am running Exchange 2003, and am looking at setting up the Conection Filter under Message Delivery to block messages based on IP address. The problem is that when I save the IPs to be blocked, I get a message stating that the Connection filter "has to be enabled manually through the specific SMTP virtual serv...

Need macro help to close excel
I have created a button in Access2000 that opens an Excel Spreadsheet. What I need now is assit in closing excel upon completion. I can get an excel macro to save my file and close the worksheet, but it is not closing excel entirely. I'm on project with this employer and could use a response today to fix this before I leave. Thanks much to any and all. My macro is as follows: Sub SaveClose() ' ' SaveClose Macro ' Macro recorded 9/27/2004 by cdjohnso ' ' Keyboard Shortcut: Ctrl+Shift+C ' ChDir "I:\SchoolsSurvey\Graphs_Reports" ActiveWorkb...

Help with simple(?) VBA function
I'm trying to selectively BOLD cells by the use of a User-Defined function. No joy. The VBA Help topics suggest something like this: Function Bold() Worksheets("Sheet1").Range("A1:A5").Font.Bold = True End Function When I try to use it the referenced cells are not changed and the function returns "0". Can anyone point this VBA neophyte in the right direction? Thanks, -Dick- Hi Dick, A function can only return a value. Macros and Functions (Macros as Opposed to Functions) http://www.cpearson.com/excel/differen.htm If all...

If Then Help!!!!
Hi! I'm stuck. I have a working macro but it needs a small tweek. The macro executes a find statement and performs calculations from the find to the end of the column. The problem is when nothing is found. I need an if statement or suggestion on how to tell it to skip the calculations if there is nothing found. This is what I have so far(with no if's): Cells.Find(What:="RIM", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Selection.End(xlToRi...

Formula Problem?
I am using Excel 2000 with Windows XP. I am having a problem. I am on Sheet 2 of my workbook. I have SSN on a sheet named Employees in the same workbook. I need to take the numbers on the Employees Sheet and transfer it to the sheet 2. I know how to do this. It just won't work. This is a copy of my formula. =SUM(Employees!C3) This should take the SSN that is in the C3 cell on the employees sheet and place it at the cell where the formula is typed. When I put this formula in the cell I am getting just a "0". Please help. =Employees!C3 -- Kind regards, Niek Otten...