# Posts by bosco_yip

• ## cutting words with Len and Left and vlookup

In L2, formula copied down :

=LEFT(A5,FIND(" ",A5)+2)

• ## function to find out format

Give few lines of example data and the expected result

• ## Count Based On Specific Criteria and Reset If Blank Encountered

In H2, formula copied down :

=IF(A2="","",COUNT(G2:G\$37)-SUM(H3:H\$37))

• ## Question a propos d'une méthode

Maybe,

In D1, formula copied down :

=IFERROR(INDEX(A\$1:A\$7,MOD(ROW(A1)-1,7)+1)&"-"&INDEX(B\$1:B\$3,INT((ROW(A1)-1)/7)+1),"")

• ## how to extract 10 digit mobile number within text or specialcharacter

Thank you it's working perfectly

and one more help if cell have 3 or 4 mobile number how to extract with this formula

One post one question.

Please open a new post for your new question, together with few lines of example data and the expected result.

• ## Count from Unique Criteria

In G2, enter formula :

=SUMPRODUCT((E2:E17=F2)/COUNTIFS(E2:E17,E2:E17,A2:A17,A2:A17,C2:C17,C2:C17))

• ## how to extract 10 digit mobile number within text or specialcharacter

B2, copied across right and all copied down :

=MID(\$A2,FIND(B\$1,\$A2)+LEN(B\$1)+2,10)

• ## Extract numbers after specific text in a text string with bracket for negative values and divide the number by 100 if letter 'c' associated with number

Then,

Change B1 formula to :

=0+SUBSTITUTE(SUBSTITUTE(LEFT(SUBSTITUTE(MID(A1,FIND("EPS",A1)+4,99)," ",REPT(" ",50)),50),"\$",""),"c","%")

and copied down

• ## Extract numbers after specific text in a text string with bracket for negative values and divide the number by 100 if letter 'c' associated with number

Try,

In B1, formula copied down :

=0+SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("EPS",A1)+4,FIND("vs",A1)-FIND("EPS",A1)-5),"\$",""),"c","%")

• ## Return Text Before Nth Instance of Particular Character

Try,

Assume data put in cell A1 and A2.

In B2 formula copied down :

=LEFT(A1,FIND("@",SUBSTITUTE(A1,"-","@",5))-1)

• ## Sumif function not working.Returning zero

Try,

=SUMPRODUCT((0+LEFT(Sheet1!B2:B27,FIND(" ",Sheet1!B2:B27)-1)>7)* Sheet1!D2:D27)

Or,

=SUMPRODUCT((0+LEFT(Sheet1!B2:B27,FIND(" ",Sheet1!B2:B27)-1)>30)* Sheet1!D2:D27)

• ## Last non-empty cell in the column based on three header rows that are criteria

If the Date column is dragged down, this formula will extract the last cell content. In fact, I want the date value in the same row.

Or, try to use INDIRECT function :

=INDIRECT("JCB!B"&MATCH(9^9,OFFSET(INDEX(JCB!1:1,INDEX(MATCH(Display!\$I\$3&Summary!\$M\$8&Summary!\$M\$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0)),,,60000)))

• ## Last non-empty cell in the column based on three header rows that are criteria

.. to extract one more value from the second column cell (B1134)..........................

To extract B1134 date value in Offset from the cell number AP1134 :

=LOOKUP(9^9,OFFSET(INDEX(JCB!1:1,INDEX(MATCH(Display!\$I\$3&Summary!\$M\$8&Summary!\$M\$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0)),,2-INDEX(MATCH(Display!\$I\$3&Summary!\$M\$8&Summary!\$M\$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0),60000))

• ## Count number of dates by month

Try,

C2, array (CSE) entry :

=SUM(IF((A2:A20>0)*MONTH(A2:A20)=B2,1))

or,

D2, normal entry :

=SUMPRODUCT((A2:A20>0)*(MONTH(A2:A20)=B2))

• ## How to automatically convert (92 Hours 29 Min) in to Minutes

Try,

In E2, formula copied down :

=IFERROR(LEFT(A2,FIND("H",A2)-2),)*60-IFERROR(LOOKUP(1,-RIGHT(LEFT(A2,FIND("M",A2)-2),ROW(\$1:\$99))),)

• ## Formula to pull data with multiple horizontal and vertical criteria

In "Summary" sheet C6, formula copied right to D6 and all copied down :

=SUMIFS(INDEX(Data!\$D\$4:\$H\$13,,MATCH(C\$5,Data!\$C\$1:\$H\$1,0)),Data!\$A\$4:\$A\$13,\$A6,Data!\$B\$4:\$B\$13,\$B6)

• ## Issue with copying index & match formula across columns

This is a 2 ways Lookup, using Index + Match function

Try to use this modified formula instead :

=INDEX(source!\$B\$4:\$GZ\$521,MATCH(\$A15,source!\$A\$4:\$A\$521,0),MATCH(1,INDEX((L\$14=source!\$B\$3:\$GZ\$3)*(L\$13=source!\$B\$2:\$GZ\$2),0),0))

Regards

• ## Multiple vlookup

Try,

In "Sheet1" D2, formula copied down :

=IF(VLOOKUP(B2,Sheet2!B:Y,24,0)=C2,VLOOKUP(B2,Sheet2!B:Y,23,0),"")

• ## Partial Match formula based on words in 2 cells

Try,

In C2, formula copied down :

=IF((MAX(MMULT(--ISNUMBER(SEARCH(MID(A2,COLUMN(INDIRECT("C1:C"&LEN(A2),)),1),B2)),ROW(INDIRECT("1:"&LEN(A2)))^0))-LEN(A2)+LEN(SUBSTITUTE(A2," ","")))/LEN(A2)>0.5,"Match","No Match")

• ## Conditional rearrangement of data

Try formula solution ,

1] In "Sheet1" E4, formula copied right to F4 and all copied down :

=IFERROR(INDEX(A\$4:A\$24,AGGREGATE(15,6,ROW(\$A\$1:\$A\$21)/((TEXT(\$A\$3:\$A\$23,"0;;0;\0")="0")),ROW(\$A1))),"")

2] In "Sheet1" G4, array (confirm pressing with Ctrl+Shift+Enter 3 keystrokes altogether) formula copied down :

=IF(F4="","",TEXTJOIN(", ",,IF(Table1_2[Column2]=F4,Table1_2[Column3],"")))