Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 393K Members
- 3,524 Online
- 424K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Which formula can use for identify the number and ignore the text

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

SOLVED
Home
## Which formula can use for identify the number and ignore the text

- Home
- :
- Excel
- :
- General Discussion
- :
- Which formula can use for identify the number and ignore the text

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2019 01:04 AM

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.

21 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2019 02:06 AM

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")`

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2019 02:18 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2019 02:47 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2019 03:08 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2019 03:22 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2019 03:35 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2019 03:47 AM

Okay, try this and see if that works for you...

`=IF(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")`

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2019 03:54 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2019 04:37 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2019 04:51 AM

Hello Subodh,

If i put data in sheet2 shell manually then your first formula is worked. But i have already plan to put there data validation by list (On sheet2 shell) (please see attachment).

So now how can i do that ?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2019 04:59 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2019 05:03 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2019 05:14 AM

Yes, that should also work.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2019 05:16 AM

Hey Subodh,

This formula is working fine but one issue again coming. You can the attachments that sheet3 have value "0" then this formula is work but if in same shell (Sheet3) not data (Blank) than this formula is not working. Same with Sheet2 shell.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2019 05:28 AM

Yes, both formula is work. what if o can put value in sheet2 shell " IT Issues 2H ". so where i have cheng the formula ?

And also see my other replay. Because new problem is coming.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2019 05:41 AM

I am busy at the moment and maybe will look at your replies later.

Btw, upload the Excel file instead of images so that other experts can also have a look at your file and tweak the formula or offer different solutions.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-03-2019 12:00 AM

Hey Subodh,

Good Morning !!!! : )

I hope today you can help me to resolve this two issue.

1) Both 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 ".

2) What if i can put value in sheet2 shell " IT Issues 2H ". so where i have Change the formula ?

Thank you in advance.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-03-2019 12:04 AM

Good morning Ashish!

You ignored my request and have posted the images again.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-04-2019 01:44 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-04-2019 04:10 AM

It seems that you don't read my replies carefully. Please read my last two replies and respond accordingly.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-06-2019 02:31 AM

Solution

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.

Best Response confirmed by
ashishG1984 (Occasional Contributor)

Related Conversations

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
85.2K
Views

4 Likes

27 Replies

Early preview of Microsoft Edge group policies

Sean Lyndersay
in
Discussions
on
06-14-2019
17.9K
Views

20 Likes

65 Replies

*Updated 9/3* Syncing in Microsoft Edge Preview Channels

Elliot Kirk
in
Articles
on
05-01-2019
34K
Views

23 Likes

201 Replies

How to download windows server 2019 update to 1903

Cmakar37
in
Windows Server for IT Pro
on
07-03-2019
10.2K
Views

0 Likes

5 Replies

$cred = Get-Credential without asking for prompts in powershell

TejCGS
in
Office 365
on
04-25-2019
18.5K
Views

0 Likes

6 Replies

This form can't be distributed as it is asking for personal or sensitive information

NZLaSalle
in
Microsoft Forms
on
07-19-2019
5,332
Views

4 Likes

56 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft