extracting numbers from a text field and applying formula

here's my question... 
i have a cell that has a text field of this type: '9-19' (a-b)
I want to create a formula that will subtract 19-9 (b-a)
and return the result as a number//

can anyone help?! is this even doable? I'd really appreciate it!

PS 
also, i'm very fairly new at excel so a detailed response with th
formula would really help!
ALe

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/14/2003 9:32:16 PM
excel 39879 articles. 2 followers. Follow

2 Replies
656 Views

Similar Articles

[PageSpeed] 5

Try:

=MID(A1,FIND("-",A1)+1,99)-LEFT(A1,FIND("-",A1)-1)

Won't attempt an explanation just yet.  If you have specific questions,
please post!

/i.


"alex10for2" <alex10for2.wwl0a@excelforum-nospam.com> wrote in message
news:alex10for2.wwl0a@excelforum-nospam.com...
>
> here's my question...
> i have a cell that has a text field of this type: '9-19' (a-b)
> I want to create a formula that will subtract 19-9 (b-a)
> and return the result as a number//
>
> can anyone help?! is this even doable? I'd really appreciate it!
>
> PS
> also, i'm very fairly new at excel so a detailed response with the
> formula would really help!
> ALex
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
v_3ismit (29)
11/14/2003 10:11:32 PM
On Fri, 14 Nov 2003 15:32:16 -0600, alex10for2
<alex10for2.wwl0a@excelforum-nospam.com> wrote:

>
>here's my question... 
>i have a cell that has a text field of this type: '9-19' (a-b)
>I want to create a formula that will subtract 19-9 (b-a)
>and return the result as a number//
>
>can anyone help?! is this even doable? I'd really appreciate it!
>
>PS 
>also, i'm very fairly new at excel so a detailed response with the
>formula would really help!
>ALex
>

One way is to download and install morefunc.xll from

 http://perso.wanadoo.fr/longre/excel/pages/Downloads.htm#Morefunc.xll.



If 9-19 is as text in A1, then:

	=-EVAL(A1)

will give you the result you want.

Note that if you enter 9-19 and it is NOT text, Excel will interpret it as a
date (9/19/2003 if you enter it today).

This EVAL function Evaluates a formula or expression that is in the form of
text and returns the result.


--ron
0
ronrosenfeld (3122)
11/15/2003 12:24:43 AM
Reply:

Similar Artilces:

Edit or Create a Partylist Lookup field
I have an issue - we have Cytrack running between our AVAYA phones and CRM 4.0 and I have enable the option that when a call comes through it opens a Phone Call activity and it automatically connects to the contact for the person calling in. Great except while we have all cleitns in as contacts - we also have clients either in as a Lead or an Opportunity when we are doign current business and I want to change the phone call activity recipient. I can do this but it comes up a "Look Up Records" screen and you havea drop down list to pick what entity you wish to locate the cl...

Matrix with imaginary numbers
I am trying to do a matrix that has real and imaginary numbers and I have no clue how to do it. I can do a matrix with real numbers, but apparently can't with the combination of both. in a2 enter a formuala =randbetween(1,10) and copy down. as thes are randomedit number they may change time to time so you copy the column A and highlight A1 edit pastespecial-values similarly formula in B2 is =randbetween(-5,5) and copy down and copy with pastespecial values in C2 type =complex(a2,B2) and copy down. you get some sort of random compled numbers. "CGA" wrote: > I...

sorting text & numbers
I have a large list that I want sorted like this: Ward 1 Ward 2 & 3 Ward 4 Ward 5 Wards 6-8 Ward 9 Ward 10 When I sort it, it says: Ward 1 Ward 10 Ward 2 & 3 Ward 4 Ward 5 Wards 6-8 Ward 9 If I just take out the words Ward and Wards, it makes some of the numbers into dates, and does something else to some other cells that returns a big number. Anything I can do? -- billjr ------------------------------------------------------------------------ billjr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25927 View this thread: http://www.excelforum.com/sh...

Formula to Autofill Info based on Other Data
Using: Excel 2000, on Windows 98 Good morning! I'm hoping that someone on here can possibly help me. I have basic Excel knowledge, but this is out of my skill set. My boss has given me a project to work on, in which I have a workbook with two worksheets that I am dealing with. The worksheets are labelled PRICE and SUMMARY. On the PRICE sheet, I have several columns. Column A lists the product name, Columns E list the per page charge for the *red* program, and Column G lists the per page charge for the *blue* program. Those are the columns we will be dealing with. All specific inform...

Lookup for Part of the number
Hi, I need to lookup for a Loan Number from another sheet. However the problem is sometimes one digit would be added to the end in the other worksheet. Is there a way to find match for this digit.For the digits in below list I need to get the match as true for 12345 and 12346 as there is 123451 and 123462 in 2nd Sheet. List in 1st Sheet Loan# 12345 12346 12347 List in 2nd Sheet Loan# 123451 123462 234728 =MATCH(A2&"*";Sheet2!A:A;0) Format both column like text! -- Regards! Stefi „Kiran” ezt írta: > Hi, > > I ne...

Access text box
I was trying to add text boxes to my database in form design view, which worked fine up until the moment when i was back in form or table view. For some reason when I add detail in one of the cells of the new column it was automatically copied to every other cell in that new column. This has only been happening with the new columns and the ones I created using the wizard when starting the database seem to be working fine. What can I do about this? On 25 mrt, 12:19, FernP <Fe...@discussions.microsoft.com> wrote: > I was trying to add text boxes to my database in form desig...

Preventing writing in a combo box field
Ok this is my problem: I've a combo box with several employee names. Of course that when the employee is chosing it's name it writes the first letter so that the combo box filters the names to the closest one. Access then higlightes the employee name execpt the letter he wrote. Employee then hits enter and Access displays its full name, has in the combo box options, neverthless I have a hidden form that copies employee name to feed other forms. The problem is that if employee chooses is name has discribed, Access will only select all the other letters of his name execpt the one ...

Removing the (1) figure before phone numbers
How can I delete the (1) figure in front of some of my phone numbers? -- phildee Delete it. Was there more to your question that you did not post? -- Russ Valentine "Phil Dee" <PhilDee@discussions.microsoft.com> wrote in message news:299FB137-E06E-43A8-825F-498EF923BB30@microsoft.com... > How can I delete the (1) figure in front of some of my phone numbers? > -- > phildee ...

I want a formula to ignore text values in cell references
If text is input into some cells referenced by a formula 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. Use ISNUMBER(): In place of A1 use =IF(ISNUMBER(A1),A1,"") -- Gary''s Student "Russellrupert" wrote: > If text is input into some cells referenced by a formula I want the formula > to ignore whatever text it finds and just calculate the result of the numbers > in cells...

Copying text value based on long (comlex) calculation
I am not sure how to even search for what I want to do, so please forgive me if my first post has been discussed and resolved ad naseum in the past... Here is my situation: - I have a workbook that contains multiple sheets. - Each sheet contains auto parts from different suppliers, sorted by part number. - Some suppliers have parts that overlap with each other and the various sheets are updated as pricing and availabilty of parts change. - I am using a combination of the VLOOKUP and MIN functions to determine which supplier has lowest cost based on a comparison of part numbers and price ac...

How I can print full text bigger than column, in repeat column
When I print statements with repeat columns in excell which have text bigger than repeat columns, complete text is printed on all sheets except first sheet. How I can overcome this problem? I want to print complete text in repeat columns, which is expanded to other columns which are blank. ...

Formula Problem
Hi I'm struggling with a formula in an excel sheet I'm working on. I recently created a time sheet for the people in my work. One person however get public holidays a different method from everyone else - she only gets half a working day. My initial formula that works fine is =IF(OR(B12="A/L",B12="S/L",B12="P/H"),7/24,IF(B12="FLEXI",0,C12-B12- D12)) My ammended ones is =IF(OR(B12="A/L",B12="S/L"),7/24,IF(B12="P/H",3.5/24),IF(B12="FLEXI", 0,C12-B12-D12)) However it doesn't want to w...

count number of weeks missing for each individual in a database
Hi, I need to measure attendace for the employees. I have a list of weeks worked (eg. below) for each employee (numbered from 1-54) and I need to work out the weeks that have not been worked and whether they are consecutive or not. Please help :-) bob jack 3 2 4 13 8 14 9 15 10 16 11 17 Hi, If Bob's weeks worked are in range B2:B7, then the weeks missing would be =52-count(B2:B7) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "pumpkin" <pumpkin@discussions.microsoft.com> wrote in message news:EC16C6E1-57C4-4F0B...

auto number a word document each time it is opened
Hi there, I would like to know how to have a number update automatically each time the document is opened. I have tried all the avenues I know and still have had no luck. I am using word 2003 and have no experience with macros, however I am keen to learn. I am struggling to get the below advice to previous users " http://www.word.mvps.org/FAQs/MacrosVBA/NumberDocs.htm. " to work for me. It keeps coming up with error messages after I have created the macro, the main one being "Compile Error: Expected Function or variable" for the "Order =" and then...

covering part of a text box border
I want to have a large text box with a border...I then want to place a smaller text box within the larger one (with its own border). However, some of the border of the large text box is vissible within the small text box. Is there a way to set the small text box on top of the larger one and partially cover the large text box border? I can not find an answer to this problem after many searches. set the smaller box on a higher layer and set a fill color on the = smaller box. "Willie Birdie" <Willie Birdie@discussions.microsoft.com> wrote in = message news:95B71C45-E4BD-...

IF formula problem
I need to add categories to a downloaded bank statement. I would use something like Pascal's CASE formula: CASE(lookup value)= a: do x b: do y c: do z etc... Excel doesn't have such formula. Therefore, I use the following nested IF formula: =IF(ISERROR(SEARCH('Netbank Cat names'!$A$2,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$3,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$4,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$5,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$6,B73)),IF(ISERROR(SEARCH('Netbank Cat names'!$A$7,B73)),"&...

Text Wrap in Excel 2002
I have been having difficultly wrapping text. I merge several cells and wrap text sucessfully. However, if I go into one of the cells and add a couple of sentences and enter, my text that was wrapped perfectly is simply a string of #########'s and I can't view the text correctly by changing the row height or reformatting (even though it is all still there - as I can see in the edit box - when my cursor is on the text box). Any suggestions? Try formatting the cell as General. bdean wrote: > > I have been having difficultly wrapping text. I merge > several cells an...

Excel Formulas
I have a spreadsheet showing whether customers have paid their debts or not. Each row represents a customer and details of the debt and the last cell in the row states whether the debt is "PAID" OR "UNPAID". Where the word "PAID" appears in a cell, I want to write a formula that will move that whole row to a different location on my sheet. I eventually want to record this action on a MACRO, so that every time I run a MACRO on my accounts spreadsheet it will take the updated information of customers marked "paid" and put in a seperate spreadsheet. Ple...

removing locked cost field from product form
On the Product entity form the 3 fields 'list price' 'standard cost' and 'current cost' are locked. Is there a way to get the 'current cost' field off the form so it can't be seen by users? thanks Garnet Hi Garnet, You can use the following Javascript Onload of the form: crmForm.all.<<name of the field>>_d.style.display = "none"; HTH, Niths "Garnet" wrote: > On the Product entity form the 3 fields 'list price' 'standard cost' and > 'current cost' are locked. Is there a way to get the &#...

computing formula according to criteria #3
Thanks, That's a good idea, the problem is I have several participants, and need a template sheet where I can just paste each participant's data and the means and SD's will be computed automatically. At the momen going over each and every participant is taking me hours. I really need a formula... -- lior ----------------------------------------------------------------------- liory's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1049 View this thread: http://www.excelforum.com/showthread.php?threadid=26138 another way to do it would be to write a ...

dates in formulas
I would like my spreadsheet to add a month to a cell based upon the value of the date in another cell. In other words, I would enter the date in A1 and be formatted as Oct-2009, and I would like to put a formula in A2 that would result in the display of Nov-2009. I've tried =A1+1 but that just won't get it as it still returns Oct -09. "Russ" <Rusty@alwaysathome.net> wrote: > I've tried =A1+1 but that just won't get it as it > still returns Oct -09. Because you added one __day__, not one month. Try one of the following: =date(year(A1),1+month(A1),da...

How do I protect only formulas in Excel?
Hi, I am looking for a solution to be able to allow some cells that are unlocked to have other users enter data into them, however I don't wan't other users to be able to enter formulas without a password. I have heard of this being done before but I didn't know if it was an option I've overlooked or some kind of advanced VB code. Any help is greatly appreciated, RH Hi, Just an idea: try catching OnChange event and check if = is entered and then popup a password dialog Hope this helps. Peter -- http://blog.jausovec.net "RHmcse2003" je napisal: >...

formula to caculate an age
I am needing a formula that would figure out the age in years and months of a specfic person. For example Date of Birth is 7/27/1999 and the date is 10/23/2004 I would like the the cell to respond as 4-2 4 being the year and 2 being the month. Have any suggestions? Gayle You need the DATEDIF Function. See Chip Pearson's site for info and usage. http://www.cpearson.com/excel/datedif.htm Gord Dibben Excel MVP On Thu, 29 Apr 2004 15:35:15 -0700, "Gayle" <anonymous@discussions.microsoft.com> wrote: >I am needing a formula that would figure out the age in >yea...

Text field size not matching
The help file says that custom fields text1-30 can have up to 255 characters but when I go to add that field to my Task Sheet it will only allow 128 characters. Any info would be greatly appreciated. Hi madsmom, I can add/view approximately 251 characters in the field when added to the table. Did you press enter when typing? If so, it will truncate at the first return. I hope this helps. Let us know how you get along. Julie Project MVP Visit http://project.mvps.org/ for the FAQs and additional information about Microsoft Project "madsmom" <madsmom@dis...

Search field no longer working
I recently converted an Access file from 97 to Office 2000. There were search fields that worked prior to the conversion. Now, when I try to search the database I get a "The control 'Find Record' the macro is attempting to search cannot be searched. If I open the database in Office 97 the search capability works fine. I did not write this program nor am I a guru in Access. Though vauge can anyone enlighten a newbie as to what may be happening? Thanks. ...