In L2, formula copied down :
=LEFT(A5,FIND(" ",A5)+2)
In L2, formula copied down :
=LEFT(A5,FIND(" ",A5)+2)
Give few lines of example data and the expected result
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.
Try,
Assume data put in cell A1 and A2.
In B2 formula copied down :
=LEFT(A1,FIND("@",SUBSTITUTE(A1,"-","@",5))-1)
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)
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)))
.. 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))
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
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],"")))