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.
You can use ISNUMBER function to identify whether the cell content is a numeric value or a text value.
Assuming you are trying to subtract the two cells A2 and B2 and both A2 and B2 may contains either a numeric value or a numeric value preceded by a space and then a text string, you may try the below formula to subtract the two cells as below...
=IF(ISNUMBER(A2),A2,TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2))))-IF(ISNUMBER(B2),B2,TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",LEN(B2))),LEN(B2))))You can use similar approach in your formula for the True part and replace the A2 and B2 in the above formula with the table cell references and see if that works for you.
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.
What I have understood as per the description so far is, a cell can either contain a numeric values or a text followed by a numeric value like "IT Issues 2" where 2 maybe any numeric value and OP is interested in 2 in this case.
I see. Another variant could be
="A" & IFERROR(" (" & 8- RIGHT(Table19[@[01-Aug-19]], LEN(Table19[@[01-Aug-19]]) -MIN(FIND({0,1,2,3,4,5,6,7,8,9},Table19 [@[01-Aug-19]]&"0123456789"))+1) - RIGHT(Table110[@[01-Aug-19]],LEN(Table110[@[01-Aug-19]])-MIN(FIND({0,1,2,3,4,5,6,7,8,9},Table110[@[01-Aug-19]]&"0123456789"))+1) & ")", "")(I didn't test), but that's practically the same.
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.
Hello,
Yes, you are right. as i said am only interested to identify only number (Which is sheet2 " IT Issues 2 ") and minus with other sheet shell (Minus alway with 8).
Now please not that, In sheet1 shell if sheet2 shell have no value then print as " A " then calculate " 8 - 2" and print " A (6) ".
I hope now you will understand and help me for it.
Thank you.
Or maybe this, replacing the condition also...
=IF(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,"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]]))))&")","A")Btw how the data gets populated in those tables? Are these manual entries or imported from an external source? If those are the manual entries, you may put a validation rule so that the table only accepts a numeric input in those table columns.