Extract numeric value from single cell

Hello,

I have numeric values within a text string in a single cell. There i
no conformity to the length, or the make-up of the text. 
There is only ever 1 numeric value (that may vary in length from 1-
digits long) within the text string.

I have used the find function, but is getting vary messy. Is there 
numeric wildcard character I can use.

Im using Excel 2000

Any help would be much appreciate

--
Landyma
-----------------------------------------------------------------------
Landyman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=807
View this thread: http://www.excelforum.com/showthread.php?threadid=26148

0
9/20/2004 3:22:17 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
921 Views

Similar Articles

[PageSpeed] 24

This formula will "extract" a numeric value from a string

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM(--ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1))))

entered with ctrl + shift & enter

-- 
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Landyman" <Landyman.1cv43z@excelforum-nospam.com> wrote in message 
news:Landyman.1cv43z@excelforum-nospam.com...
>
> Hello,
>
> I have numeric values within a text string in a single cell. There is
> no conformity to the length, or the make-up of the text.
> There is only ever 1 numeric value (that may vary in length from 1-5
> digits long) within the text string.
>
> I have used the find function, but is getting vary messy. Is there a
> numeric wildcard character I can use.
>
> Im using Excel 2000
>
> Any help would be much appreciated
>
>
> -- 
> Landyman
> ------------------------------------------------------------------------
> Landyman's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=8070
> View this thread: http://www.excelforum.com/showthread.php?threadid=261486
> 


0
terre081 (3244)
9/20/2004 3:48:34 AM
here is another way, it is in VBA. it can be used like any othe
worksheet function, like, =GETNUM(A1).

put this code in a separate module, not in the worksheet's cod
module.

Public Function GetNum(varString As Variant)
Dim i As Long, n As Long, x As String, strTemp As String
n = Len(varString) 'length of the input string
If n < 1 Then Exit Function
strTemp = ""
For i = 1 To n
If IsNumeric(Mid(varString, i, 1)) Then
x = Mid(varString, i, 1) 'pick the byte if numeric
strTemp = strTemp & x 'append it to the string
End If
Next i
If Len(strTemp) = 0 Then
GetNum = "No digits found"
Else
GetNum = CDbl(strTemp) 'convert string to DOUBLE
End If
End Functio

--
icestationzbr
-----------------------------------------------------------------------
icestationzbra's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=458
View this thread: http://www.excelforum.com/showthread.php?threadid=26148

0
9/20/2004 5:22:07 AM
<< This formula will "extract" a numeric value from a string

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM(--ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1))))

entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum) >>

Hello Peo,

Your formula works very well and I am keeping it in my files because 
have a similar application for it. I just need to ask you this ... wha
is the meaning of "1:100" in your formula?

I really need to understand it so I can make the required adjustment
when I do you formula in my application.

Thanks

--
BenjieLo

-----------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1101
View this thread: http://www.excelforum.com/showthread.php?threadid=26148

0
9/20/2004 3:47:18 PM
I'm not Peo, but 100 is the limit that Peo placed on the length of the string in
A1.

If your strings are longer than 100, change all the 100's (1:100 twice and
100-sum once) to a number larger than the length of your string.

You could even modify Peo's formula slightly:

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),
     LEN(A1)-SUM(--ISERROR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))

(still all one cell and still ctrl-shift-enter (array entered))

BenjieLop wrote:
> 
> << This formula will "extract" a numeric value from a string
> 
> =--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM(--ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1))))
> 
> entered with ctrl + shift & enter
> 
> --
> Regards,
> 
> Peo Sjoblom
> 
> (No private emails please, for everyone's
> benefit keep the discussion in the newsgroup/forum) >>
> 
> Hello Peo,
> 
> Your formula works very well and I am keeping it in my files because I
> have a similar application for it. I just need to ask you this ... what
> is the meaning of "1:100" in your formula?
> 
> I really need to understand it so I can make the required adjustments
> when I do you formula in my application.
> 
> Thanks.
> 
> --
> BenjieLop
> 
> ------------------------------------------------------------------------
> BenjieLop's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11019
> View this thread: http://www.excelforum.com/showthread.php?threadid=261486

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/20/2004 9:23:56 PM
Wow ... 100 for the length of string in a cell!!! I guess that's a very
long string.

Thank you for the explanation, Dave ... it is always helpful to
understand the terms in a formula in case some adjustments have to be
made later on. I will also keep your formula in my files (side by side
with Peo's). It is always nice to have more than 1 formula handy ...
makes me look like I know what I am doing!!!

Regards and once again, thanks a lot.


<< I'm not Peo, but 100 is the limit that Peo placed on the length of
the string in
A1.

If your strings are longer than 100, change all the 100's (1:100 twice
and
100-sum once) to a number larger than the length of your string.

You could even modify Peo's formula slightly:

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),
LEN(A1)-SUM(--ISERROR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))

(still all one cell and still ctrl-shift-enter (array entered))   >>


-- 
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11019
View this thread: http://www.excelforum.com/showthread.php?threadid=261486

0
9/20/2004 9:40:08 PM
If you're using xl2002 or higher, put a small string in A1 (less than 10
characters) and then change all those 100's to 10.

Then select that cell with the formula and 
Tools|formula auditing|evaluate formula 
And you'll see how excel evaluates this very nice formula.

BenjieLop wrote:
> 
> Wow ... 100 for the length of string in a cell!!! I guess that's a very
> long string.
> 
> Thank you for the explanation, Dave ... it is always helpful to
> understand the terms in a formula in case some adjustments have to be
> made later on. I will also keep your formula in my files (side by side
> with Peo's). It is always nice to have more than 1 formula handy ...
> makes me look like I know what I am doing!!!
> 
> Regards and once again, thanks a lot.
> 
> << I'm not Peo, but 100 is the limit that Peo placed on the length of
> the string in
> A1.
> 
> If your strings are longer than 100, change all the 100's (1:100 twice
> and
> 100-sum once) to a number larger than the length of your string.
> 
> You could even modify Peo's formula slightly:
> 
> =--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),
> LEN(A1)-SUM(--ISERROR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))
> 
> (still all one cell and still ctrl-shift-enter (array entered))   >>
> 
> --
> BenjieLop
> 
> ------------------------------------------------------------------------
> BenjieLop's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11019
> View this thread: http://www.excelforum.com/showthread.php?threadid=261486

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/20/2004 10:09:28 PM
Reply:

Similar Artilces:

Formula cells needs to display "zero"
How do i get a cell with a formula in it to display a zero until a figure is typed into a corresponding cell? All productive help is greatly appreciated. -- thank you Assuming your formula is SUM(B1:B20), try: =IF(A1="",0,SUM(B1:B20)) Regards, KL "hello" <hello@discussions.microsoft.com> wrote in message news:6716AFD9-2CE5-4E56-9863-45760F652082@microsoft.com... > How do i get a cell with a formula in it to display a zero until a figure > is > typed into a corresponding cell? > All productive help is greatly appreciated. > -- > thank you ...

Leave Zero's in cell
Hi, when I put three zero's into a cell,.excel automatically changes it back to one zero. I want to keep the three zero's in the cell. Woiuld appreciate any advice please Regards Ivan Hi Ivan Either: Format the cell as Text (although then the numbers entered are text rather than genuine numerics) or format the cells to show leading zeroes (eg a custom format like 000) - note that this is simply for display purposes as the actual contents of the cell will be 0. Hope this helps1 Richard On 15 Mar, 10:47, "Ivan B" <nos...@nospam.net> wrote: > Hi, > when...

Tracking item quantities and values
I'm wondering if there is a way to track the quantity of items in a category or department over a history of time, including the total value of those categories or departments at that specific time. For example, I tried a report on Reports > Items > Value List and was able to get extended quantities and extended cost for the current time, but I am unable to change the time in the filter. I have also tried Reports > Items > Item Movement History and changed the range to Year to Date. This includes the change in quantity, but not the change in extended cost. It seems like ...

Can not see CString's value when debugging
I'm very sure I'm in the CString's life scope, but I can not see its value but only some "???". Any other type of variables are ok. But some days ago, in the same project, I can see CString's value when debugging. So, what's wrong of CString in debug mode? Now I nearly can't do the debugging job! >So, what's wrong of CString in debug mode? It's a limitation of the debugger. Newer versions of CString have optimisations for the storage of small/long strings that the debugger doesn't cater for :( Dave ...

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

Excel VB-Copy formula down until adjacent cell (left) is blank?
Here is exactly what I am trying to do through VB in Excel: Weekly data pull fills colums A:G. Row count is always different. I am modifying the data pull through VB, and I have a VLOOKUP formula in cell H2. What I want VB to do is copy that formula down column H to the last row (with data) each week. I guess I want it to be dynamic so that as rows decrease/increase the formula is only copied down to the final row/record. I know someone out of this smart group will know how to do this! Thanks in advance! Tony (pseudo code) in a macro .... dim lngLastrow as long dim rngTarget...

Making a worksheet and a graph that doesn't show zero values
Hi I am trying to build a template worksheet for my research. I get a file of logged data per day. I want to be able to copy the logged data from a .CSV file to a template file. Then separate worksheets will look at the raw data and import data from certain columns into that worksheet. The data is not logged in a regular way, there can be 30 cells between logging intervals or 29 or 31 cells. I can select that every cell in a column of Worksheet 1 looks at a set cell in the "Raw Data" worksheet. This will then give me a column consisting of logged data values with zeroes ...

hyperlinks & cell reference
Hi folks, Everytime I insert a new column into my Excel table the hyperlinks which are linked to the cells don't change automaticlly. Thus I've to change every following hyperlink per hand. Is there an option to relate hyperlinks to an other reference point than the cells? Thank you for your time in advance Joerg If I gave the range a nice name, I could use that in my hyperlink: Using the =hyperlink() worksheet formula: =HYPERLINK("#testname","your friendly name here") TestName referred to c9. Inserting a new column and now it referred to D9. Joerg Ce...

extracting icons
Is there any way to extract an icon from an application? Take a look at the ExtractIcon[Ex]() API Cheers Check Abdoul --------------------- <davegreb@gpxinc.com> wrote in message news:c6c54da6-574c-4c8e-8ec9-3b77b99a5523@d70g2000hsb.googlegroups.com... > Is there any way to extract an icon from an application? ...

Hiding some labels on value axes; Changing series names
I've created a column chart in Excel 2002. It has 9 columns, increasing in value from left to right. The 8 columns on the left have values ranging from 2.7 to 12. The 9th column has a value of 51.8. The primary y axis shows the values for the 8 columns, while the secondary y axis shows the value for the 9th column. First question. The primary y axis scale goes from 0 to 16 with a major unit of 4. The secondary y axis scale goes from 0 to 55 with a major unit of 5. I would like to hide some of the numbers shown on each axis, so as to make it clearer which axis is the relevant one for each...

How do I separate address information from within an Excel cell?
I have a Christmas Card list that was created using a single cell for the whole address. Street, City, Zip/Postal etc. I need to split those components out into separate cells in order to do a proper mail merge. Is there any way to do this. I am not completely unfamiliar with macros but I if that is the solution I would require a fair bit of hand holding. Thanks S. If you actually used commas to separate the fields (and none of the fields contain commas), you could select the column and do: Data|text to columns delimited comma Remember to keep enough open columns to the right s...

Redistribution of values after reaching maximum
Hello, I am trying to build a formula to to resolve the following scenario: Column A Column B Row 1 $2,000 Row 2 $25,000 $222.22 Row 3 $35,000 $311.11 Row 4 $45,000 $400.00 Row 5 $55,000 $488.89 Row 6 $65,000 $577.78 Row 7 $225,000 $2,000 In this table, A2 through A6 represent salaries (A7 is the total). B1 is the amount of the bonus pool that is to be divided proportinately to salary. B2 is calculated as =(A...

delay in displaying cell contents
Is there a simple function that can delay the display of the contents of a cell by a few seconds. I know that it can be done as VBA code but I dont know how to do it. Thanks in advance for any help. Dave Hi have a look at the Wait method in the VBA help -- Regards Frank Kabel Frankfurt, Germany "Dave" <dave-rawlins@beeb.net> schrieb im Newsbeitrag news:9995bde6.0410151425.1688ff24@posting.google.com... > Is there a simple function that can delay the display of the contents > of a cell by a few seconds. I know that it can be done as VBA code > but I dont know how...

finding a cell from numerical coordinates #2
is it possible to locate a cell from coordinates such as 50, 75 or 100,98? I'd really like to be able to do this. No need to post the question twice so soon. In 2007 press Alt+1, Formulas tab and check R1C1 reference style -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Chip" wrote: > is it possible to locate a cell from coordinates such as 50, 75 or 100,98? > I'd really like to be able to do this. Hi, In 2007 press Alt+1, Formulas tab and check R1C1 reference style -- If this helps, please click the Yes button. Cheers, Shane Dev...

Displaying a calculated value
I wanted to calculate the current age of a contact using information from another column i.e date of birth. I used a formula available in tutorials and it worked. I see the current age in an text box on the form. When I go to the datasheet I do not see the age. The value is 0. What do I do to show the age in the datasheet. Do I need to create another column with some formula? Because I need queries that show ages of contacts. Thanks in advance. Deeds37 - You do not want to store the age in the database - it is always changing! Instead, calculate it on any forms or in any ...

Dynamically lookup value based upon column heading (date)
Hi, Is it possible to dynamically look up a value based upon matching a a date column heading? e.g. Sheet 1 ---Jan---Feb---March---April x---1-----2-------3------BLANK-- y z Sheet 2 ---Jan---Feb---March---April Q--1-----2-------3-------3--- Thus i'm trying to achieve a formula that can match column heading (date) on one sheet with column heading on another, then look down that column to find a value. However as a twist on this, I need it to be able to use the last "completed" value should the match not exist or the value is blank. In the above example, on Sheet 2 I ...

only show last value entered in a row
Need formula to only show last value entered in a row.. Thanks To -- Message posted from http://www.ExcelForum.com On Tue, 4 May 2004 20:10:08 -0500, picktr <<picktr.15rdmw@excelforum-nospam.com>> wrote: >Need formula to only show last value entered in a row.. > >Thanks > >Tom > > >--- >Message posted from http://www.ExcelForum.com/ See answer in .misc Please post only to one group. Most read all the groups and it only fragments efforts. --ron ...

DMM Duplicate List Values
well, this is my first real cut at trying to use the 4.0 DMM, and I have to say it sucks. I want to go back to the 3.0 version. (at least I could manipulate the data in the DM tables if I needed to). And I can't get it to install on the SQL Server box either. anywho, all I want to do is assign different input values to a one picklist value - if its ON, on, Ont, Ontario,,,,, I want the picklist value to be 'Ontario' - just a little light data cleansing. I can't do it in the Mapping tool in CRM - I can do it using the 'sample' mapping editor, but then the DMM thr...

Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract and then sum up certain numerals from this entire range. The catch is that the data is mixed numerals and text, as you'll see below. Here's an abbreviated 3x3 example, with a value in each of the nine cells: V7.1 T H P1 A T B V3 P4.5 If I just wanted to sum up the instances of "T" appearing, I could use COUNTIF() for the entire range to come up with answer ("T" appears 2 times). Easy enough. But, what I'm trying to accomplish is to sum up the numerals associated w...

Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd like to extract to column B. Is there a formula I can use to accomplish extracting the email address only to column B? Here's an example of different cells in column A: Please email example@law.ufl.edu to contact us...... OR Schedule an appointment for assistance, or email example@uga.edu with your questions... Thank you Try this... All on one line: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",...

Value-of Select
Hi there, Im in a pickle chaps. I need to fetch information out from our raw XML files into a Style XSL sheet. Basically I have the commant <xsl:value-of select="Product/ProductCode"/> However the structure of the XML file is based like the following example <root> <Product> <ProductCode type="Seller">V1145</ProductCode> <ProductCode type="Buyer">H05439</ProductCode> </Product> </root> As you can see, we have different product codes based on Buyer and Sellers. However in my style sheet I only wan...

Query and Extract
Hello all, This seems basic, but I just cannot get it. While using SQL Server 2005 and the Microsoft SQL Server Management Studio, I created a database called AssetQuote. Inside I have on table called assetquotes. From there, I have three columns, (date, quote, author) The column type for date is datetime and the other two are just text. My Query is something this: INSERT INTO AssetQuote (date, quote, author) VALUES('052010','No act of kindness, no matter how small, is ever wasted.', 'Aesop'); When I run the query I get this result: Msg 208, ...

extracting individual numbers from "ranges"
I'm trying to get EXCEL to extract stock prices quoted in individual cells as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation: (higher price - lower price)/lower price Obviously, this a problem because each cell appears to handle the range as text or something. Is there are way to get EXCEL to handle the "text" (or however else it is interpreting what's in the cell) as individual numbers? Great thanks for any help. Hi do your entries always look like lower_number-higher_number if yes you may try the following formula in the adjacent row (lets assum...

How to get cell coordinates?
As previously posted, I am trying to build a formula that updates its cell references automatically based on what line the formula is entered. My formula will be more complex but for the sake of simplicity, here's an example of what I'm trying to achieve... A1 = 100 A2 = 25 A3 = C3 B1 = 50 B2 = 10 B3 = C3 C3 = formula that adds A1+A2 or B1+B2, depending on the row number. Please remember that this is a simple example and that my actual formula will be more complex and therefore, an IF statement will be too long for what I want to achieve. In other words, my formu...

most frequently occurring value
hello how do i find out the most frequently occurring value, digit, or numbe in a set of rows & columns ? then i need to find out the second most occurring value then the 3rd then find the least frequently occurring value then the second least occurring value then the 3rd thanx.. -- Pivotren ----------------------------------------------------------------------- Pivotrend's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=406 View this thread: http://www.excelforum.com/showthread.php?threadid=49572 Look in HELP for the FREUQUENCY() function -- Kind reg...