how do i sum a total in excel with numbers and text 58kg + 60kg

I can't seem to find a formula to add a column of numbers that have text, e.g 
50kg+60kg+75kg won't add - can anyone help

Many thanks
0
Bryan1 (123)
11/22/2005 10:47:04 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
517 Views

Similar Articles

[PageSpeed] 56

Whenever you need to use numbers and text together and also need 
computations, use custom formatting:


Pick an empty cell and pull-down:
Format > Cells > Number > Custom and enter General"kg"
if you then enter 100 in that cell, it will appear as 100kg, but you can 
still use it for computations.
-- 
Gary's Student


"Bryan" wrote:

> I can't seem to find a formula to add a column of numbers that have text, e.g 
> 50kg+60kg+75kg won't add - can anyone help
> 
> Many thanks
0
GarysStudent (1572)
11/22/2005 11:03:07 AM
Hi Gary - thanks - the problem here is that I am using a weigh scales program 
that exports a txt file to excel and I need to sum the total but it is coming 
across as xxkg already so when I applied your suggestion it didn't add. I 
appreciated your help though. Thanks from Australia!

"Gary''s Student" wrote:

> Whenever you need to use numbers and text together and also need 
> computations, use custom formatting:
> 
> 
> Pick an empty cell and pull-down:
> Format > Cells > Number > Custom and enter General"kg"
> if you then enter 100 in that cell, it will appear as 100kg, but you can 
> still use it for computations.
> -- 
> Gary's Student
> 
> 
> "Bryan" wrote:
> 
> > I can't seem to find a formula to add a column of numbers that have text, e.g 
> > 50kg+60kg+75kg won't add - can anyone help
> > 
> > Many thanks
0
Bryan1 (123)
11/22/2005 11:18:02 AM
Hi Bryan

Try
=--SUBSTITUTE(A1,"kg","")
this will turn your numbers from text 50kg to 50

Regards

Roger Govier


Bryan wrote:
> Hi Gary - thanks - the problem here is that I am using a weigh scales program 
> that exports a txt file to excel and I need to sum the total but it is coming 
> across as xxkg already so when I applied your suggestion it didn't add. I 
> appreciated your help though. Thanks from Australia!
> 
> "Gary''s Student" wrote:
> 
> 
>>Whenever you need to use numbers and text together and also need 
>>computations, use custom formatting:
>>
>>
>>Pick an empty cell and pull-down:
>>Format > Cells > Number > Custom and enter General"kg"
>>if you then enter 100 in that cell, it will appear as 100kg, but you can 
>>still use it for computations.
>>-- 
>>Gary's Student
>>
>>
>>"Bryan" wrote:
>>
>>
>>>I can't seem to find a formula to add a column of numbers that have text, e.g 
>>>50kg+60kg+75kg won't add - can anyone help
>>>
>>>Many thanks
0
roger1272 (620)
11/22/2005 11:32:38 AM
Thats it - thanks very much to both of you

Regards Bryan

"Roger Govier" wrote:

> Hi Bryan
> 
> Try
> =--SUBSTITUTE(A1,"kg","")
> this will turn your numbers from text 50kg to 50
> 
> Regards
> 
> Roger Govier
> 
> 
> Bryan wrote:
> > Hi Gary - thanks - the problem here is that I am using a weigh scales program 
> > that exports a txt file to excel and I need to sum the total but it is coming 
> > across as xxkg already so when I applied your suggestion it didn't add. I 
> > appreciated your help though. Thanks from Australia!
> > 
> > "Gary''s Student" wrote:
> > 
> > 
> >>Whenever you need to use numbers and text together and also need 
> >>computations, use custom formatting:
> >>
> >>
> >>Pick an empty cell and pull-down:
> >>Format > Cells > Number > Custom and enter General"kg"
> >>if you then enter 100 in that cell, it will appear as 100kg, but you can 
> >>still use it for computations.
> >>-- 
> >>Gary's Student
> >>
> >>
> >>"Bryan" wrote:
> >>
> >>
> >>>I can't seem to find a formula to add a column of numbers that have text, e.g 
> >>>50kg+60kg+75kg won't add - can anyone help
> >>>
> >>>Many thanks
> 
0
Bryan1 (123)
11/22/2005 11:46:11 AM
Reply:

Similar Artilces:

Shortcuts in Explorer not working for Excel, but opens within Excel
Shortcuts in Explorer not working for Excel, but opens within Excel (periodically) Thought it might be memory...still may be the issue, but not sure. See one reply at your previous post. "Brian @ SMS" wrote: > > Shortcuts in Explorer not working for Excel, but opens > within Excel (periodically) > > Thought it might be memory...still may be the issue, but > not sure. -- Dave Peterson ec35720@msn.com ...

Excel recently used function is greyed out
I recently restored my office xp program and downloaded and added the service pac 3. Upon running my excel xp program I notice that I can no longer see recently opened files. I have gone to "Tools","Options","General" and the "recently used file" text window and box are all greyed out. Any help would be appreciated Thanks, Marc Hi Marc! One possibility: Some anti-spyware and utilities like Tweakui have options to disable all MRU (most recently used) lists. Biff >-----Original Message----- >I recently restored my office xp program and down...

Other charges to include in billing after sub-total
It is becoming very much essential to charge Fuel Surcharge in every billing besides Fedex charges. Does any one using RMS, created a special code besides Shipping, to charge Customers? We are loolking to create one which would not be included in the Sub-total of items, but below sub-total and add to Grand Total. Any tips will be appreciated. ...

Remove Hyphens from a number
I have numbers in a coulmn that look like this: 01-012-4123 how do I remove only the hyphens (dashes) Select your range and Edit|Replace What: - (hyphen) with: (leave blank) You may want to format those cells (custom: 000000000) to keep the leading 0's. Mike wrote: > > I have numbers in a coulmn that look like this: > > 01-012-4123 how do I remove only the hyphens (dashes) -- Dave Peterson ec35720@msn.com Add a column with something like this in . =SUBSTITUTE(A1,"-","") Robin Hammond www.enhanceddatasystems.com "Mike" <anonymous...

Install FrontPage for excel?
Hi. Lucky me...first post on this forum but thanks in advance if you can help. I recently performed an update. Since today I notice all programs seem to open without problem except for excel. It wants me to insall excel with frontpage before opening. Naturally it does not open and I don't have a CD with frontpage. What shall I do? How can I redirect all existing documents to excel without frontpage and also ensure future docs. don't need it? -- thanks....jkstar jkstar - > ... It wants me to insall excel with frontpage before opening. ... < First, what is the &...

How to perform sum sum sum...
How to perform sumation within that particular item but the item is not unique...means item 1 has its own quantity and same goes to item 2...but in the same table... -- Message posted via http://www.accessmonster.com On Tue, 17 Apr 2007 05:28:12 GMT, "EMILYTAN via AccessMonster.com" <u33296@uwe> wrote: >How to perform sumation within that particular item but the item is not >unique...means item 1 has its own quantity and same goes to item 2...but in >the same table... Group By the item. For a more detailed answer, please post a more detailed question (with a des...

Excel upgrade
I just bought an upgrade version, the package says that if i have works 6-10 this version will work for me. The sales clerk said I would need to buy the full version. Anyone know aabout this. -- Ger Upgrade from what to what? See here for qualifying products for upgrade to 2007 editions. http://office.microsoft.com/en-us/products/FX101754511033.aspx Looks like the sales clerk may be trying for a larger commission<g> Works 6-10 is OK according to the list as long as you aren't upgrading to Office Basic or Home and Student. Gord Dibben MS Excel MVP On Wed, 5 Sep 2007 15:58...

Summing up user defined results
Hi all - I'm new to VBA programming in Excel and so any help i'd totally love! I'm currently writing a function. Objective: user can select rows (do not have to be sequential). User clicks on button. UserForm appears with summed results from ONLY rows that he selected. What I have now, well it doesn't work: Sub Button6_Click() Dim i As Integer Dim totalNumbers As Integer Dim aRange As range For Each a In Selection.Areas 'MsgBox "Area " & i & " of the selection contains " & _ ' a.Rows.Count & " rows." &...

View/Edit CSV/Excel in HTML
Hi, I have a requirement in which i want to display a CSV file from the server to the user, allow him to make changes to the CSV file and he should be able to submit back the changes to the server. Please advice. Thanks, Aatish ...

Pasting unformatted text
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I cannot find an 'Edit > Paste Special... > Unformatted Text' command to assign to a keyboard shortcut. Can you help please? <br><br>I have no idea how to use AppleScript or Automator. <br><br>fh Nope, not an option. The script you need is freely available though. See this page for how to take advantage of it, then click the last link on that page to retrieve the script; http://word.mvps.org/mac/Scripts/index.html HTH |:>) Bob Jones [MVP] Office:Mac On 1/28/10 6:28 AM,...

Hyperlink in Excel opens the right program, but can't read file.
I use many hyperlinks to direct users to the correct files. Even if I use a hyperlink to another program, the program opens and the files are viewed in the correct program. I am having a unique problem to a program called "editNC" (file extensions .ncc). The program opens when you click on the hyperlink that is in my Excel workbook, but I get the error message "Unable to Read File" after the editNC program window opens. Also, up on the top bar of the editNC program it is saying that the file is "read only". If I open the file directly from editNC, I ha...

Excel find gives exception
Hi, I get a Range object from an Excel worksheet object in VC++. I use .olb files to import the tlbs. I have a problem with Find() method of the Range object. I get a "scalar delteing destructor" exception and get a HRESULT of -2146827284.This happenes only for the 2nd parameter of the Find(). VARIANT WhatVt; WhatVt.vt = VT_BSTR; WhatVt.bstrVal = ::SysAllocString(L"PATIENT"); VARIANT AfterVt; VariantInit(&AfterVt); AfterVt.vt = VT_BSTR; AfterVt.bstrVal = ::SysAllocString(L"A1"); VARIANT LookInVt; LookInVt.vt = VT_ERROR; Loo...

Build a link to an other excell document.
Hi! I've managed to link fileds to an other excell document. IE. ='C:\[calc2009.xls]Page1'!A1 But now I'm trying to build a link so that it updates with a new year every time you change the year. IE. in B1 I write the year 2010. Now the link in B2 changes to ='C:\[calc2010.xls]Page1'!A1 I've tried with ='C:\[calc&B1&.xls]Page1'!A1 and similiar, but with no success. Is this possible? Thanks for your help! The function you'd want to use is =indirect(). But =indirect() won't work if the sending file is closed. Laurent...

Multiple variables to sort and sum, return values<0 with sum refer
Can anyone help me with this one please? I have this table of data. I'd like to be able to write a formula(s) which sums the No according to date and code, but then only returns a sum value (with the code and date in the two adjacent cells) if there is a value greater than 0. Code Ref No Date 1 G/032/05/999 400600 212 19/03/2010 2 G/032/03/001/999 400500 50 19/03/2010 3 G/032/02/001/001 400400 170 19/03/2010 4 G/032/05/999 400600 315 19/03/2010 5 G/032/03/001/999 400500 300 19/03/2010 6 G/032/05/999 400600 202 19/03/2010 7 G/032/03/001/002 40...

sum subform to form
=Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged]) Form (frmCamPledgeList) with subform frmCamPledgeListSub). Subform has a field named Pledge Amount. I want to put a field on the form that gives me a total of the subform's Amount Pledged. I have tried the above, but I get an #error in the unbound text box. Do you need any more info? Any thoughts on why this does not work? Thanks in advance, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1 The proper syntax is =Sum([Forms]![frmCa...

Excel File in Use Notification Missing??
We are using Windows Server 2000 & several versions of Excel (2000 / XP / 2003). When opening Excel files from the network, SOME users get the Notification that the File is In Use - locked for editing by <user> - with a choice to Open as Read-Only, Notify when avail, or Cancel. The pblm is that NOT EVERYBODY gets this msg - some simply have the file open up with the word (Sharing) in the Title Bar!! When you've got a deskful of work to input you're not always looking at the Title Bar to see if the file is Shared or not. I first thought this was caused by diff vers o...

Allow Excel to orient/align text 180 degrees
Is this possible? Are you sure you mean 180 degrees? This would leave the text upsidedown and backwards which cannot be done unless you take a picture of the tert and rotate it. Do you perhaps mean "reverse the order of the text from front to back" which would leave it rightside up with letters reversed? That can be done with a User Defined Function. Public Function RevStr(Rng As Range) RevStr = StrReverse(Rng.text) End Function Gord Dibben Excel MVP On Thu, 6 Jan 2005 13:11:06 -0800, "QuadJoe4x4" <QuadJoe4x4@discussions.microsoft.com> wrote: >Is t...

Calculate sum of a field within the query
I have a query that returns the details for a failure record during a specified time period. Where if I search between 1/1/08 and 1/31/08 the query returns the records of all failure records during that period. The row shows the part number and the total parts failed for that record. I need the query to then sum the total part failures for the previous 12 months. So I would see the following: Record # | part # | parts failed | Origination Date| Total Parts failed past 12 months 44444 | x | 4 | 1/2/08 | 200 44445 | Y |1 ...

Prevent sequential number being used if record not saved
I have an invoice form that when opened, uses the following code to allocate the next invoice number (InvNo): Private Sub Form_BeforeInsert(Cancel As Integer) Me.InvNo = Nz(DMax("[InvNo]", "[tblInvoices]")) + 1 Me.Dirty = False End Sub Trouble is, if I then decide for some reason not to carry on, even if I click on the Close button rightaway without having entered anything anywhere, the invoice number is still used and appears in the Invoices table (without any values in any of the fields, of course). How can I prevent this? Ideally I would like to be able to...

I'm trying to get a custom graph in Excel
I'm trying to graph the sales development of one sales territory when compared to the overall sales development of the corporation; the Y-axis values should be in percentage, the X-value axis is in years 1995 through 2004. I like to have it graphed similar to a stock graph, however with both sales data starting at 0% and then showing an upper (+) or lower (-) sales development over the last 10-years. My problem so far is that the corporate sales is so large that the territory sales is too small and not very well visible. If I change the corporate sales by eliminating the last digit...

Excel Menu Bar
The excel Menu Bar has "disappeared" from the screen. How do I get it back? Help is needed urgently. Thanks! right-click on any toolbars. choose customize on the pop-up menu. on the dialog that appear there is a list of toolbars. look for worksheet menu bar and check. click ok. "Esme Jooste" <esme.jooste@resbank.co.za> wrote in message news:0af401c39882$4b599b80$a301280a@phx.gbl... > The excel Menu Bar has "disappeared" from the screen. > How do I get it back? Help is needed urgently. > > Thanks! ...

Problem Calculating Various Month Totals ??
hello, I need a formula that will SUM values for particular Months within column (B4:B1000)... I am trying to use a formula that will identify the first 3 letters o the month and then add the currency (G4:G1000) for those months only.. every formula I am attempting to use is not working..:confused: I used this to calculate the totals for February, but it fails: =SUMIF(B4:B1000,"FEB*",G4:G1000) G4:G1000 contains the amounts for various months. The months varie through column B4:B1000.. Any help on this is Appreciated... eg. ITEM DATE AMOUNT _____ ...

Analysis cubes with excel sql 2005 issues
The documentation on this is very poor. So I installed the product for the demo company TWO on GP 9 SP2. 1. To load the data from Great Plains to data warehouse, I am able to run the package from visual studio and the data populates the data warehouse. However, when I create a job, the job fails. Can you guide me into how to create a job for this? 2. I was able to load the cubes by running the package through visual studio. However, I dont know how to view these cubes and assign security to them. What is the way to do this in SQL 2005. 3. Thereafter I went to Great Plains and provide...

how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a running total of my supplies ie... I have 2 pens, remove one and receive 2. answer in the cell becomes 3 pens then if I zero out the received cell the inventory cell still remembers that I have 3 pens not 4 pens (because I received 2 more pens) and visa versa when I zero out the received cell it remembers I have 3 pens not 1 pen(because I removed 1 pen) I believe the method you suggest is flawed. You have no audit trail. If the number on the sheet doesn't match your actual inventory, how will you figure out...

VBA Excel
Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Looking to purchase an imac for home use and also want to learn VBA in XL for work proficiency. Based on the VBA 2008 issues, what would you suggest to be the best environment to learn VBA... <br><br>A. Wait for new Office edition later this year <br> B. Run Office apps in parallel (does this solve VBA issues?) <br> C. Install Office 2008 On 2010-05-28 13:57:55 -0400, trust123@officeformac.com said: > Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Looking > to purchase a...