Forum Discussion
Which formula can use for identify the number and ignore the text
Hello,
I have some issue to calculate one sheet to another. Because my first sheet has number and text in one shell and same thing in other sheet with drop down list. Please see below. How i can solve this issue ?
Thank you in Advance.
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.
21 Replies
You can wrap your formula within IFERROR to handle the error like this...
=IFERROR(IF(Table19[@[01-Aug-19]]>0,"A ("&8-Table19[@[01-Aug-19]]-Table110[@[01-Aug-19]]&")","A"),"A")Hello,
Thank you for your advice. But i tried already and is give me answer noly "A". Is not calculate the number from other sheet.
If you look on sheet2 than you will see that there is " IT Issues 2 ". However, i need that whenever i do calculation on sheet1 cell, just identify number which is in sheet2 (" IT Issues 2 ") and ignore the text.
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.
