Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to extract number values only
Hi,
I can't seem to remember how to use a formula to extract numbers only. For example, I would like to take a create a formula to pull only "3.5" for a cell that containts "Sum: 3.5"... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to extract number values only
In your example, you could use:
=--(RIGHT(A2,3)) If your cells follow a pattern of: Text(space)number You can use: =--(RIGHT(A2,LEN(A2)-FIND(" ",A2))) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "April" wrote: Hi, I can't seem to remember how to use a formula to extract numbers only. For example, I would like to take a create a formula to pull only "3.5" for a cell that containts "Sum: 3.5"... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to extract number values only
April,
you only give one example so it's a bit hit annd miss I'm afraid but how about this =LOOKUP(6.022*10^23,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$100)))) all in one line of course Mike "April" wrote: Hi, I can't seem to remember how to use a formula to extract numbers only. For example, I would like to take a create a formula to pull only "3.5" for a cell that containts "Sum: 3.5"... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to extract number values only
cell that containts "Sum: 3.5"...
If the number is *always* after the space: A1 = Sum: 3.5 =--MID(A1,FIND(" ",A1),15) -- Biff Microsoft Excel MVP "April" wrote in message ... Hi, I can't seem to remember how to use a formula to extract numbers only. For example, I would like to take a create a formula to pull only "3.5" for a cell that containts "Sum: 3.5"... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to extract number values only
On Fri, 20 Nov 2009 13:39:01 -0800, April
wrote: Hi, I can't seem to remember how to use a formula to extract numbers only. For example, I would like to take a create a formula to pull only "3.5" for a cell that containts "Sum: 3.5"... If the number is always at the end of the string, then: =--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2,3,4, 5,6,7,8,9,0")),15) --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to extract number values only
I can't seem to remember how to use a formula to extract numbers only. For
example, I would like to take a create a formula to pull only "3.5" for a cell that containts "Sum: 3.5"... If the number is always at the end of the string, then: =--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2,3,4, 5,6,7,8,9,0")),15) I think that will fail if there are other numbers appearing earlier in the string. Here is a method to pull the number at the end no matter what is in front of it... =LOOKUP(9.9E+307,--RIGHT(A1,ROW($1:$99))) -- Rick (MVP - Excel) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to extract number values only
On Fri, 20 Nov 2009 22:40:53 -0500, "Rick Rothstein"
wrote: I think that will fail if there are other numbers appearing earlier in the string. It will. That's why I wrote: If the number is always at the end of the string, then: --ron |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to extract number values only
Another one
=--MID(A1,FIND(":",A1)+1,9) If this post helps click Yes --------------- Jacob Skaria "April" wrote: Hi, I can't seem to remember how to use a formula to extract numbers only. For example, I would like to take a create a formula to pull only "3.5" for a cell that containts "Sum: 3.5"... |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to extract number values only
Sorry, I misread what you meant by that... I thought you were referring to
the number that the OP was after, as per his example, being at the end of the text no matter what what in front of it. -- Rick (MVP - Excel) "Ron Rosenfeld" wrote in message ... On Fri, 20 Nov 2009 22:40:53 -0500, "Rick Rothstein" wrote: I think that will fail if there are other numbers appearing earlier in the string. It will. That's why I wrote: If the number is always at the end of the string, then: --ron |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to extract number values only
On Sat, 21 Nov 2009 23:27:11 -0500, "Rick Rothstein"
wrote: Sorry, I misread what you meant by that... I thought you were referring to the number that the OP was after, as per his example, being at the end of the text no matter what what in front of it. -- Rick (MVP - Excel) As we've both noted before, figuring out all the possible variants, given an initial limited posting of examples, can be tough, and lead to initial inappropriate responses. For example, what if there could be multiple numbers, but only the last set of numbers is wanted; but there could be text following. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract values off a chart??? | Charts and Charting in Excel | |||
extract number and use in formula from text & numbers in cell | Excel Worksheet Functions | |||
Extract number from text/number string.. | Excel Discussion (Misc queries) | |||
Formula to pick out number values | Excel Worksheet Functions | |||
Extract Unique Values, Then Extract Again to Remove Suffixes | Excel Discussion (Misc queries) |