Forum Discussion
Which formula can use for identify the number and ignore the text
Hello,
Good Morning !!!! : )
1) Formula is working fine (See the below images Sheet1,2,3). If you can see the below images that sheet3 have value "0" then this formulas is work but if in same shell (Sheet3) not data (Blank) ( Sheet11,12,13 ) than this formulas is not working. Same with Sheet2 shell.
It should work like that, If sheet2 have data and sheet3 no data (Blank) then sheet1 do calculation (8-Sheet2 shell) but if sheet2 have no data as well sheet3 no data then sheet1 should print only " A ".
Formula :
=IF(Table19[@[01-Aug-19]]="Bank Holiday","Bank Holiday",IF(Table19[@[01-Aug-19]]="Holiday","Holiday",IF(Table19[@[01-Aug-19]]="Unpaid Leave","Unpaid Leave",IF(AND(IF(ISNUMBER(Table19[@[01-Aug-19]]),Table19[@[01-Aug-19]],TRIM(RIGHT(SUBSTITUTE(Table19[@[01-Aug-19]]," ",REPT(" ",LEN(Table19[@[01-Aug-19]]))),LEN(Table19[@[01-Aug-19]]))))>=0,IF(ISNUMBER(Table110[@[01-Aug-19]]),Table110[@[01-Aug-19]],TRIM(RIGHT(SUBSTITUTE(Table110[@[01-Aug-19]]," ",REPT(" ",LEN(Table110[@[01-Aug-19]]))),LEN(Table110[@[01-Aug-19]]))))>=0),"A ( "&8-IF(ISNUMBER(Table19[@[01-Aug-19]]),Table19[@[01-Aug-19]],TRIM(RIGHT(SUBSTITUTE(Table19[@[01-Aug-19]]," ",REPT(" ",LEN(Table19[@[01-Aug-19]]))),LEN(Table19[@[01-Aug-19]]))))-IF(ISNUMBER(Table110[@[01-Aug-19]]),Table110[@[01-Aug-19]],TRIM(RIGHT(SUBSTITUTE(Table110[@[01-Aug-19]]," ",REPT(" ",LEN(Table110[@[01-Aug-19]]))),LEN(Table110[@[01-Aug-19]]))))&"H )","A"))))
Please if you can help me.
Thank you in advance.
Hey Sergei,
I have one thing to ask you. Is Dynamic Arrays Functions are available in Office 365 Pro Plus Version 1808 ?
If not then what i have to do for get this function ?
I am so glad if i get your feedback.
Thank you in advance.
Dynamic Arrays are available for Office 365 subscribers Insiders Fast and Monthly Targeted (former Insiders Slow) channels. I didn't check, but build 1808 most probably is on semi-annual channel, it has no Dynamic Arrays so far.