TechArena Community Looking for formula that can ignore text values in Excel
 User Name Remember Me? Password

Sponsored Links

# Looking for formula that can ignore text values in Excel

## MS Office Support

#1
23-02-2012
 Member Join Date: Feb 2012 Posts: 13
Looking for formula that can ignore text values in Excel

 Sponsored Links
If text is input into some cells referenced by a formula, then I want the formula to ignore whatever text it finds and just calculate the result of the numbers in cells. How do i do this. Presently the formula displays #VALUE! where there is text in one or more of the cells referenced.

#2
23-02-2012
 Member Join Date: Jul 2011 Posts: 589
Re: Looking for formula that can ignore text values in Excel

You can try the following. Use ISNUMBER(): In place of A1 use =IF(ISNUMBER(A1),A1,"").
#3
23-02-2012
 Member Join Date: Jul 2011 Posts: 584
Re: Looking for formula that can ignore text values in Excel

Just some thoughts. If it's to sum numbers, we could use, eg: =SUM(A1:A3) instead of : =A1+A2+A3. SUM will ignore text, while : =A1+A2+A3 would return #VALUE! if there are text within the range. And if there's the possibility of "text" numbers within the range, which should also be included in the sum together with real numbers, then one way is to try, array-entered (press CTRL+SHIFT+ENTER): =SUM(IF(ISNUMBER(--A1:A3),--A1:A3)) [ =SUM(A1:A3) would ignore text numbers ].
#4
23-02-2012
 Member Join Date: Jul 2011 Posts: 571
Re: Looking for formula that can ignore text values in Excel

Please forgive my ignorance, I am looking to do something similar here. I am looking to divide the contents of cell B21 by the contents of cell A27 on my Excel sheet. The problem I am encountering is cell A27 contains the following "9 Waitresses" and I am looking to have the formula disregard the text "Waitresses". Can anyone tell me how to do this.

 Thread Tools Search this Thread Search this Thread: Advanced Search

 Similar Threads for: "Looking for formula that can ignore text values in Excel" Thread Thread Starter Forum Replies Last Post How to put formula in Text Box in Microsoft Excel TheCarter MS Office Support 2 03-02-2012 04:58 PM Convert copy formula to Text in Microsoft Excel Kaesav MS Office Support 2 01-02-2012 06:39 PM I need help with a text formula Len Cobb Windows Software 1 31-08-2010 01:15 AM Setting input text values Solaris Software Development 3 23-11-2009 01:42 PM Text not visible in cell only in Formula Bar (excel 2003) NavinS MS Office Support 1 14-02-2008 02:32 AM

All times are GMT +5.5. The time now is 09:25 PM.

 Contact Us - TechArena - Privacy Statement -