Pages

9 May 2012

=Rept("Formula", "Tips")

Tip 1: The below formula uses to extract the last name from Full Name entries. Assume, Name is in A1.

 =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))
 
Tip 2: Recently i have been solved this query on FB with the help of REPT formula. I hope that It will be useful for advanced formula users.

Question:



Answer:

 C4 Formula,

=IFERROR(SUBSTITUTE(TRIM(MID(REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND("Colors",REPT(" ",999)&SUBSTITUTE(B4,"/",
REPT(" ",999)),(1*999))-25,100)),"Colors",""),"")

C5 Formula,

=IFERROR(IF(C4="","",SUBSTITUTE(TRIM(MID(REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND("Colors",REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND(C4,REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),1)+25)-25,100)),"Colors","")),"")

C6 Formula,

=IFERROR(IF(C5="","",SUBSTITUTE(TRIM(MID(REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND("Colors",REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND(C5,REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),1)+25)-25,100)),"Colors","")),"")

C7 Formula,

=IFERROR(IF(C6="","",SUBSTITUTE(TRIM(MID(REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND("Colors",REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),FIND(C6,REPT(" ",999)&SUBSTITUTE(B4,"/",REPT(" ",999)),1)+25)-25,100)),"Colors","")),"")

 The same formula can be use it for other entries by copy paste method.

Sample file - Download Link

No comments:

Post a Comment