ignoring text data in a cell when summing values in the same cell

I have entered text in cells along with a numerical value as a note pertainig 
to the values example, "2 - exist". Is there a way to tell Excell to ignore 
the text data in order to sum the values..do you place the text in brackets 
or parathesis or something similar?
-- 
FM
0
Utf
2/23/2010 10:51:02 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
1326 Views

Similar Articles

[PageSpeed] 37

Much, much easier never to mix text & numbers in the same cell in the 1st 
place. One avenue here, try use Data>Text to Columns to split the numbers 
separate from the text, then you can sum (or whatever) the numbers col 
directly. 
Or, assuming your data is representative (ie numbers are to the left of the 
dash "-") and runs in A2 down, you could place this in B2: 
=LEFT(A2,SEARCH("-",A2)-1)+0
and copy down, then SUM col B
Any joy? hit YES to wave it 
-- 
Max
Singapore
--- 
"FM" wrote:
> I have entered text in cells along with a numerical value as a note pertainig 
> to the values example, "2 - exist". Is there a way to tell Excell to ignore 
> the text data in order to sum the values..do you place the text in brackets 
> or parenthesis or something similar?

0
Utf
2/23/2010 11:15:01 PM
>2 - exist

Does every cell follow that same format? A number followed by a space 
followed by a dash?

Does every cell contain a number?

-- 
Biff
Microsoft Excel MVP


"FM" <FM@discussions.microsoft.com> wrote in message 
news:8F29DF75-E1DD-4C23-96DE-243D7CBBD921@microsoft.com...
>I have entered text in cells along with a numerical value as a note 
>pertainig
> to the values example, "2 - exist". Is there a way to tell Excell to 
> ignore
> the text data in order to sum the values..do you place the text in 
> brackets
> or parathesis or something similar?
> -- 
> FM 


0
T
2/24/2010 2:51:14 AM
Reply:

Similar Artilces:

ignore list
I have importet some contact data into mscrm, When I want to add these contacts to a marketing list (add marketing list members / use advanced find/ add all selected members), the adding stops with an error. I have done a trace during the error (occurs everytime I want to add these contacts) which shows me the following error: [2009-08-24 11:15:36.778] Process:OUTLOOK |Thread:5884 |Category: Unmanaged.Platform |User: PlatformUser |Level: Error | Found crmId {319C876A-CC39-DC11-9F61-0030485C3892} in ignore list. Update notification will be ignored Function: CItemHelper<struct Outlook::_Co...

create a data entry form template in Excel 2003?
In previous versions of Excel it was possible to create a data entry form template by using the template wizard add in. How do you do this in Excel 2003. ...

Text highlighting BUT HOW ?!?!
Hello how can i highlighting a Text in a control. Must i crerate a new Control by using dne CWin-Class Or can i override the OnPaint-Function by the CEdit-Class In the control i must coloread a line and the line can breaked Sorry guys i have no idea. ; Reinharde Use a rich edit control, and set the font or the color for the text you want highlighted. joe On Thu, 27 May 2004 05:16:03 -0700, "Reinharder" <anonymous@discussions.microsoft.com> wrote: >Hello, > >how can i highlighting a Text in a control. Must i crerate a new Control by using dne CWin-Class...

Reports not returning correct data #2
I am trying to run the Top Sales Rep Report in HQ and in SO Manager. The problem is they are not returning the same data for each store. The HQ report is also missing some of the Sales Reps. The SO Manager report gives the correct data and all the sales reps. Also, the numbers are off. For instance, Store 1 in SO Manager might show that Rep. A sold $5200 and Store 1 in HQ might show that he only sold $4800. Anyone having any other trouble like this? Please help, my financial person is on my ass!!! Nick ...

Protect cells #2
Is there a way to protect cells that has programing or funcions, WITHOUT locking the sheet. To lock the sheet is very bothersome that prevents me from unhiding and formating etc. -- Jacob Fleischman Federal plastics ltee 514-342-5411 jacob@fedplast.com No. Protection is only invoked when the book or ws is protected. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "JACOB" <JACOB@FEDPLAST.COM> wrote in message news:ehbM9l%23ZDHA.1640@TK2MSFTNGP10.phx.gbl... > Is there a way to protect cells that has programing or funcions, WITHOUT > locking the shee...

Can You Have A Font's Properties Change When You Click The Cell?
First and foremost, I've just spent some time reading the posts in this ng and have learned more than a few tricks, that as a novice, I would've never been able to complete. With that in mind a big thanks goes out to the many contributors here. That being said, here is what I'm trying to accomplish: In a cell, I've got the letter 'A', and it's color is grey. When I click on it, I'd like it to become black and bold. Is there any way to accomplish this? Also, if I were to click the cell again, it would return to it's original state. I suppose I would not be...

IF to ignore #N/A
Hi, I have a string of IF conditions, which I am combining with SUM. I want to get the IF conditions to ignore any cells containg the error message #N/A. How can I do this? Thanks, Bertie. -- claytorm ------------------------------------------------------------------------ claytorm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11610 View this thread: http://www.excelforum.com/showthread.php?threadid=398660 claytorm Wrote: > Hi, > > I have a string of IF conditions, which I am combining with SUM. I want > to get the IF conditions to ignore ...

Trouble opening Outlook data file
Hi. I archived all my emails and saved the .pst file before reformatting my hard drive. Now that I have everything installed again, I try to open the archive.pst in outlook, and it tells me that I do not have permission to access the file. Does anyone know how I can get all my emails back from the archive file? TIA Never mind. I didn't have permission because I was trying to open it from the cd I placed it on. I guess you need write access to open the thing. Once I placed it on my hard drive, I was able to access it and extract my emails. "Silmarillion" <evan.dickso...

Compare dates to copy data
Help please... How would I get the following to occur. (Sheet 1 cell A1 = 2/22/10 when I run macro, look for 2/22/10 on sheet 2, and copy an area from sheet 1 to the appropriate area of sheet 2. So each time sheet one date changes, it copys the same area of sheet one into the correct area of sheet 2 that matches the date. -- Thank you for your time! John If it was me, I would do the following: 1. Turn on macro recorder. 2. Go to Sheet2, filter the data for the date you want. 3. Copy the data to its destination. 4. Turn off macro recorder. 5. Modify the macro the pickup the ...

Project duration based upon lump sum cost
Hello, I am a consultant who works on lump sum projects. I would like to use MS Project to calculate project durations and Gantt charts based upon resources (and associated bill rates) combined with their work allocation percentage. I've tried several times to make this work and have even tried to make my own custom fields but can't seen to get all the pieces / formulas to work out. Can anyone tell me if this should be possible w/o resorting to custom VBA programming? Thank you! What you are trying to do is what Project is designed to do. Before getting into a lo...

TTS
I'm looking for text to speech so that a person could call their voicemail and have their e-mail read to them over the phone. I hear Microsoft has this in the works for the next version of Exchange, we'll see. Avaya has the capability to integrate w/ Exchange to give TTS but it's extremely expensive and I'm not a big Avaya fan. Is anyone else doing this with any other product? On Sat, 30 Jul 2005 02:49:27 GMT, Terry Dalton <terence_daltonNOSPAM@hotmailDOT.com> wrote: >I'm looking for text to speech so that a person could call their voicemail >and ha...

Want to plot a graph using data from 2 different worksheets in sam
workbook. I want to take 2003, 2004 sales and 2005 sales on same graph. Data is in the same file with different worksheet, I cant seem to plot it. I had to copy all the data on same worksheet and then plot it. But there must be a way to do it without this action. cteq1@hotmail.com Try charting one year then: Chart; Source Data; Add to add the rest. "cteq" <cteq@discussions.microsoft.com> wrote in message news:C9FA73D4-E26C-4AE7-B6C6-F3A8E7AEBADA@microsoft.com... > workbook. > > I want to take 2003, 2004 sales and 2005 sales on same graph. Data is in > the &...

Gathering Data into a List
Lets say that I have a group of cells ten columns by three rows with some of the cells containing text. Which cells have text and which are blank will change every now and then. I want to create a column that gathers all data from the above group of cells and puts them into a list. Blank cells will not be displayed and the column will gather the data for the first cell from A1, the second from A2, the third from A3, the fourth from B1, etc... unless of course the referenced cell is blank, in which case it would gather the next bit of text. How do I do this? Here is the way to do ...

Counting Text #2
I have a list of text (e.g:- True False True True False I want the cell which will count how many times False appears in thi column of cells. I assume its a fairly easy function but I dont kno which one!! Cheers Mar -- Message posted from http://www.ExcelForum.com COUNTIF (cell range, "FALSE") --- Message posted from http://www.ExcelForum.com/ If it's really text--not boolean TRUE/FALSE, then how about: =sumproduct(--(a1:a1000="false")) "locutus243 <" wrote: > > I have a list of text (e.g:- > > True > False > True > Tru...

Dashboards and Source Data
Hello, I created a dashboard with several small graphs on it. The data for the graphs is stored on a separate data sheet (i.e., work sheet). I now want to duplicate the dashboard/data sheets to build a dashboard for another employee. I envision the workbook eventually having 5 dashboards driven off 5 data sheets. The trick I'm looking for is a way to change the datasource for the graphs on the copied datasheet to the appropriate corresponding data sheet. I know how to do it manually but it seems like there should be some sort of "find and replace" type f...

appending unbound combo query data to new record
This may be covered elsewhere, but my understanding of how to phrase the question is limited at best. To set the scene - this is a dB for a weekly running club. I have a table "RunDetails" (holds details of runners in an event) which has a lookup field (the date) bound to table "Runs" (details of the event itself). Form PersData displays the details of runners (name, etc). On this form I have two unbound texboxes (Bib and Distance) and a date combobox (cbo.rowsource is query from table 'Runs') and a button to enter their ID, and the contents of t...

How can I get my text to arc in Visio?
I have a circular pie chart. I want my text in various places on the outside of the ciruclar pie chart to arc with the circle. I know how to add text boxes, but they don't arc--they are flat across the usual way (since visio doesn't support it) is to copy it from something like powerpoint. al "michobrien" <michobrien@discussions.microsoft.com> wrote in message news:E6593E93-8E62-4BEE-87F4-79B395F11F91@microsoft.com... >I have a circular pie chart. I want my text in various places on the >outside > of the ciruclar pie chart to arc with the circle. I k...

Sorting ignoring alpahnumeric lists ignoring "the" and "an".
I am trying to sort a large alphanumeric list of titles, but wish to do so igniring definitive articles such as "The" and "an". Is there anyone that can tell me how to do this? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ create a dummy column using =Substitute(Substitute(substitute(A1,"the ",""),"an ",""),"And ","") then drag fill down the column. Sort on the column with the formul...

Cell Values #2
How, by using VB can you differentiate between a blank cell and a cel with the value of zero? Thanks -- jtrevil ----------------------------------------------------------------------- jtrevill's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1660 View this thread: http://www.excelforum.com/showthread.php?threadid=31503 See possible answer in your other thread -- Regards Ron de Bruin http://www.rondebruin.nl "jtrevill" <jtrevill.1fxhpz@excelforum-nospam.com> wrote in message news:jtrevill.1fxhpz@excelforum-nospam.com... > > How, by usi...

How to look up a value in a list and return multiple corresponding
I followed the instructions on the excel help page of how look up a value in a list and return multiple corresponding values but somethings not right. I have tried this numerous times in my excel spreadsheet and it does not return any values let alone numerous values. No values will appear unless I go into the insert menu and click function, but then here it will only give me the smallest value, even when there is more than one. Please help asap! Thank you. How about giving us some details? -- Biff Microsoft Excel MVP "123456789" <123456789@discussions.m...

Adding Time Cell Was Changed
Column A has different values (these are not important). In column B I want to show the time that say cell A1 was changed. I have tried several methods but all result in all the times in column B being changed to the same time. Can anyone help? Many thanks. see http://www.mcgimpsey.com/excel/timestamp.html In article <532607C4-B90C-4963-81DB-88F93753D803@microsoft.com>, "Andy Rads" <ajrsar@blueyonder.co.uk> wrote: > Column A has different values (these are not important). In column B I want > to show the time that say cell A1 was changed. I have trie...

Stop ignoring me! Emails on behalf of.
I have a rule that moves emails from 'bob@random.rnd' * to another folder. But my rule does not cope when the email comes in like "H.R.Department@random.rnd on behalf of bob@random.rnd" then the rule does not work anymore. How can I write a rule to cope with this sort of annoying email? * Email address has been changed to protect privacy use a rule that looks for words in the header - and use bob@random.rnd as the words? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Aut...

Text In Cells Query
Can I Split Text into two lines within one Cell Right click in the cell you wish to enter text in and select format cells, then select Alignment and check the Wrap text box. Go back to your sell and type the info you want. -- MaggieB "bambiethree" wrote: > Can I Split Text into two lines within one Cell Just press Alt+ENTER to break for a new line within a cell -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "bambiethree" wrote: > Can I Split Text into two lines within one Cell Think it's easier to use Alt+ENTER to force a new line ...

Add addional data on top of bar chart data
How do I add a dotted/shaded stack on top of an existing one. Ex: I have 2 stacks that represent revenue shipped in each of 4 Quarters . Then in the next quarter I have revenue shipped to date. Now I need to add projected revenue to ship on top of the shipped data in this 1 Quarter only. The bottom data ( shipped to date ) is a solid color, and I need to be able to show the projected data on top either shaded or in some other method so you can see the shipped to date with the projected in top clearly. ...

row height not accounting for wrap text
I have a column formatted to wrap text but the text wrap is defeated because the row heights do not automatically adjust to fit the wrapped text. That is, the text gets clipped below the first line because the row height stays at one line high. Some rows contain a single line of text, but some have two lines, but they ALL get clipped to a single line because the row height does not adjust. This is very frustrating as I need to print these reports and no one will know what the heck the text says if they only see 1/2 of it! Any ideas? -- cwinters -------------------------------------...