Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 14th 08, 06:03 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2008
Posts: 3
Default Help Extracting Numbers formula

Help!!

Is there a way for excel to identify cells with number in them, then extract
the numerical data from the cell and use it in another formula?


--
Kwokman

  #2   Report Post  
Old June 14th 08, 06:35 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default Help Extracting Numbers formula

Need more detail.

--
Biff
Microsoft Excel MVP


"Kwokman3" wrote in message
...
Help!!

Is there a way for excel to identify cells with number in them, then
extract
the numerical data from the cell and use it in another formula?


--
Kwokman



  #3   Report Post  
Old June 14th 08, 06:38 PM posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default Help Extracting Numbers formula

Perhaps something like this, using ISNUMBER:
In E1: =IF(ISNUMBER(A1),A1*SUM(B1:C1),"")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kwokman3" wrote:
Is there a way for excel to identify cells with number in them, then extract
the numerical data from the cell and use it in another formula?
--
Kwokman

  #4   Report Post  
Old June 14th 08, 06:48 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2008
Posts: 3
Default Help Extracting Numbers formula

Ok....sample...

If a given spreadsheet has...

3 days [in cell A1]
400 per day [in cell A2]

then in cell A3, I'd like to multiply 3 and 400.
Mind you, the cell has mixed text and numbers.

Some idiot submitted a huge spreadsheet without separating units from number
of units!!



--
Kwokman


"T. Valko" wrote:

Need more detail.

--
Biff
Microsoft Excel MVP


"Kwokman3" wrote in message
...
Help!!

Is there a way for excel to identify cells with number in them, then
extract
the numerical data from the cell and use it in another formula?


--
Kwokman




  #5   Report Post  
Old June 14th 08, 07:01 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default Help Extracting Numbers formula

Assuming the number is always the first characters of the string followed by
a space:

=LEFT(A1,FIND(" ",A1)-1)*LEFT(A2,FIND(" ",A2)-1)

--
Biff
Microsoft Excel MVP


"Kwokman3" wrote in message
...
Ok....sample...

If a given spreadsheet has...

3 days [in cell A1]
400 per day [in cell A2]

then in cell A3, I'd like to multiply 3 and 400.
Mind you, the cell has mixed text and numbers.

Some idiot submitted a huge spreadsheet without separating units from
number
of units!!



--
Kwokman


"T. Valko" wrote:

Need more detail.

--
Biff
Microsoft Excel MVP


"Kwokman3" wrote in message
...
Help!!

Is there a way for excel to identify cells with number in them, then
extract
the numerical data from the cell and use it in another formula?


--
Kwokman








  #6   Report Post  
Old June 14th 08, 07:02 PM posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default Help Extracting Numbers formula

3 days [in cell A1]
400 per day [in cell A2]


Maybe this, in A3:
=LEFT(A1,SEARCH(" ",A1)-1)*LEFT(A2,SEARCH(" ",A2)-1)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

  #7   Report Post  
Old June 14th 08, 07:07 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,389
Default Help Extracting Numbers formula

Hi,
If all your data starts with numbers and has a space between the number and
the text, as per your samples:

Assuming data in column A, starting in A2, try this in B2, copied down:
=LEFT(A2,FIND(" ",A2,1)-1)

Regards - Dave.
  #8   Report Post  
Old June 14th 08, 07:56 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2008
Posts: 3
Default Help Extracting Numbers formula

This works!! Thank you!!

For my knowledge, if you have the time to respond,
how do the LEFT() and FIND() functions work?

--
Kwokman


"T. Valko" wrote:

Assuming the number is always the first characters of the string followed by
a space:

=LEFT(A1,FIND(" ",A1)-1)*LEFT(A2,FIND(" ",A2)-1)

--
Biff
Microsoft Excel MVP


"Kwokman3" wrote in message
...
Ok....sample...

If a given spreadsheet has...

3 days [in cell A1]
400 per day [in cell A2]

then in cell A3, I'd like to multiply 3 and 400.
Mind you, the cell has mixed text and numbers.

Some idiot submitted a huge spreadsheet without separating units from
number
of units!!



--
Kwokman


"T. Valko" wrote:

Need more detail.

--
Biff
Microsoft Excel MVP


"Kwokman3" wrote in message
...
Help!!

Is there a way for excel to identify cells with number in them, then
extract
the numerical data from the cell and use it in another formula?


--
Kwokman






  #9   Report Post  
Old June 15th 08, 03:49 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default Help Extracting Numbers formula

Let's use this string for an example:

A1 = 3 days

=LEFT(A1,FIND(" ",A1)-1)

The LEFT function returns the specified number of characters of a string
starting from the leftmost character. We use FIND to determine the specified
number of characters.

Since the part of the string we want to extract is immediately before the
first space we find the location of that first space and then subtract 1 for
the space character itself. We use the FIND function to tell us where that
first space character id located.

3 days

FIND(" ",A1) = 2

The space is the second character in the string. We subtract 1 for the space
character itself so:

FIND(" ",A1)-1 = 1

This 1 is then passed to the LEFT function telling it we want to extract 1
character starting from the leftmost of the string "3 days".

So, the result of this formula is 3. It's important to note that the LEFT
function returns a TEXT value. Using this example the "3" we extracted is a
TEXT number not a numeric value. When combined with the other segment of the
entire formula:

=LEFT(A1,FIND(" ",A1)-1)*LEFT(A2,FIND(" ",A2)-1)

The TEXT numbers are coerced to numeric values by performing a math
operation of them. So, the final result of the entire formula is numeric
1200.


--
Biff
Microsoft Excel MVP


"Kwokman3" wrote in message
...
This works!! Thank you!!

For my knowledge, if you have the time to respond,
how do the LEFT() and FIND() functions work?

--
Kwokman


"T. Valko" wrote:

Assuming the number is always the first characters of the string followed
by
a space:

=LEFT(A1,FIND(" ",A1)-1)*LEFT(A2,FIND(" ",A2)-1)

--
Biff
Microsoft Excel MVP


"Kwokman3" wrote in message
...
Ok....sample...

If a given spreadsheet has...

3 days [in cell A1]
400 per day [in cell A2]

then in cell A3, I'd like to multiply 3 and 400.
Mind you, the cell has mixed text and numbers.

Some idiot submitted a huge spreadsheet without separating units from
number
of units!!



--
Kwokman


"T. Valko" wrote:

Need more detail.

--
Biff
Microsoft Excel MVP


"Kwokman3" wrote in message
...
Help!!

Is there a way for excel to identify cells with number in them, then
extract
the numerical data from the cell and use it in another formula?


--
Kwokman








  #10   Report Post  
Old May 31st 09, 05:28 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 91
Default Help Extracting Numbers formula

This was soooooooo helpful. Can I add to this question? If I use the left -
find formula to pull out a number from a text string, I know I can calculate
using this formula but can I take the results and sum it? I have a list of
file sized in various units (KB, MB, GB) and I used an "IF" formula along
with the left-find function noted below to convert all of the text strings
into KB. BUt now I can't sum those results. Is there a way to do this?

"T. Valko" wrote:

Let's use this string for an example:

A1 = 3 days

=LEFT(A1,FIND(" ",A1)-1)

The LEFT function returns the specified number of characters of a string
starting from the leftmost character. We use FIND to determine the specified
number of characters.

Since the part of the string we want to extract is immediately before the
first space we find the location of that first space and then subtract 1 for
the space character itself. We use the FIND function to tell us where that
first space character id located.

3 days

FIND(" ",A1) = 2

The space is the second character in the string. We subtract 1 for the space
character itself so:

FIND(" ",A1)-1 = 1

This 1 is then passed to the LEFT function telling it we want to extract 1
character starting from the leftmost of the string "3 days".

So, the result of this formula is 3. It's important to note that the LEFT
function returns a TEXT value. Using this example the "3" we extracted is a
TEXT number not a numeric value. When combined with the other segment of the
entire formula:

=LEFT(A1,FIND(" ",A1)-1)*LEFT(A2,FIND(" ",A2)-1)

The TEXT numbers are coerced to numeric values by performing a math
operation of them. So, the final result of the entire formula is numeric
1200.


--
Biff
Microsoft Excel MVP


"Kwokman3" wrote in message
...
This works!! Thank you!!

For my knowledge, if you have the time to respond,
how do the LEFT() and FIND() functions work?

--
Kwokman


"T. Valko" wrote:

Assuming the number is always the first characters of the string followed
by
a space:

=LEFT(A1,FIND(" ",A1)-1)*LEFT(A2,FIND(" ",A2)-1)

--
Biff
Microsoft Excel MVP


"Kwokman3" wrote in message
...
Ok....sample...

If a given spreadsheet has...

3 days [in cell A1]
400 per day [in cell A2]

then in cell A3, I'd like to multiply 3 and 400.
Mind you, the cell has mixed text and numbers.

Some idiot submitted a huge spreadsheet without separating units from
number
of units!!



--
Kwokman


"T. Valko" wrote:

Need more detail.

--
Biff
Microsoft Excel MVP


"Kwokman3" wrote in message
...
Help!!

Is there a way for excel to identify cells with number in them, then
extract
the numerical data from the cell and use it in another formula?


--
Kwokman











Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extracting Numbers from string Keyrookie Excel Worksheet Functions 4 October 4th 07 11:47 PM
Extracting Numbers Richard Excel Discussion (Misc queries) 1 July 16th 07 07:43 PM
Extracting numbers from a text AshMorK Excel Discussion (Misc queries) 2 November 15th 06 10:22 PM
Extracting numbers James Silverton Charts and Charting in Excel 2 September 11th 06 04:33 AM
extracting numbers cj Excel Worksheet Functions 28 February 9th 06 04:01 AM


All times are GMT +1. The time now is 06:54 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017