|
[ Pobierz całość w formacie PDF ]
.F 8/27/01 11:58 AM Page 546546 Part V: Miscellaneous Formula TechniquesNotice that the result isn t perfect.For example, it will not work if the cellcontains only one name (for example, Madonna).And, this method also failsif a name has two middle names (such as John Jacob Robert Smith).Thatoccurs because the formula simply searches for the second space characterin the name.In this example, the megaformula returns John Robert Smith.Later in this chapter, I present an array formula method to identify the lastspace character in a string.With a bit of work, you can eliminate all the intermediate formulas and replacethem with a single megaformula.You do so by creating all the intermediate formu-las and then editing the final result formula (in this case, the formula in column H)by replacing each cell reference with a copy of the formula in the cell referred to.Fortunately, you can use the clipboard to copy and paste (see the previous sidebar, Copying Text from a Formula ).Keep repeating this process until cell H1 containsnothing but references to cell A1.You end up with the following megaformula inone cell:=LEFT(TRIM(A1),FIND( ,TRIM(A1),1)-1)& &RIGHT(TRIM(A1),LEN(TRIM(A1))-IF(ISERROR(FIND( ,TRIM(A1),FIND( ,TRIM(A1),1)+1)),FIND( ,TRIM(A1),1),FIND( ,TRIM(A1),FIND( ,TRIM(A1),1)+1)))When you re satisfied that the megaformula works, you can delete the columnsthat hold the intermediate formulas because they are no longer used.THE STEP-BY-STEP PROCEDUREIf you re still not clear about this process, take a look at these step-by-stepprocedures:1.Examine the formula in H1.This formula contains two cell references (F1and G1):=F1& &G12.Activate cell G1 and copy the contents of the formula (without the equalsign) to the clipboard.3.Activate cell H1 and replace the reference to cell G1 with the clipboardcontents.Now cell H1 contains the following formula:=F1& &RIGHT(B1,LEN(B1)-E1)4.Activate cell F1 and copy the contents of the formula (without the equalsign) to the clipboard. 4800-x Ch20.F 8/27/01 11:58 AM Page 547Chapter 20: Creating Megaformulas 5475.Activate cell H1 and replace the reference to cell F1 with the clipboardcontents.Now the formula in cell H1 is:=LEFT(B1,C1-1)& &RIGHT(B1,LEN(B1)-E1)6.Now cell H1 contains references to three cells (B1, C1, and E1).The for-mulas in those cells will replace each of the three references.7.Replace the reference to cell E1 with the formula in E1.The result is:=LEFT(B1,C1-1)& &RIGHT(B1,LEN(B1)-IF(ISERROR(D1),C1,D1))8.Notice that the formula in cell H1 now contains two references to cell D1.Copy the formula from D1 and replace both of the references to cell D1.The formula now looks like this:=LEFT(B1,C1-1)& &RIGHT(B1,LEN(B1)-IF(ISERROR(FIND( ,B1,C1+1)),C1,FIND( ,B1,C1+1)))9.Replace the four references to cell C1 with the formula contained in cellC1.The formula in cell H1 is:=LEFT(B1,FIND( ,B1,1)-1)& &RIGHT(B1,LEN(B1)-IF(ISERROR(FIND( ,B1,FIND( ,B1,1)+1)),FIND( ,B1,1),FIND( ,B1,FIND( ,B1,1)+1)))10.Finally, replace the nine references to cell B1 with the formula in cell B1.The result is:=LEFT(TRIM(A1),FIND( ,TRIM(A1),1)-1)& &RIGHT(TRIM(A1),LEN(TRIM(A1))-IF(ISERROR(FIND( ,TRIM(A1),FIND( ,TRIM(A1),1)+1)),FIND( ,TRIM(A1),1),FIND( ,TRIM(A1),FIND( ,TRIM(A1),1)+1)))Notice that the formula in cell H1 now contains references only to cell A1.Themegaformula is complete, and it performs exactly the same tasks as all the inter-mediate formulas (which you can now delete).You can access the workbook for removing middle names and initials onthe companion CD-ROM.COMPARING SPEED AND EFFICIENCYBecause a megaformula is so complex, you may think that using one slows downrecalculation.Actually, that s not the case.As a test, I created a workbook that usedthe megaformula 65,536 times.Then I created another workbook that used six 4800-x Ch20.F 8/27/01 11:58 AM Page 548548 Part V: Miscellaneous Formula Techniquesintermediate formulas to compute the 65,536 results [ Pobierz całość w formacie PDF ] |
Archiwum
|