|
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 |
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 |
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 |
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 |
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 |
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 --- |
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. |
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 |
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 |
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 |
Help Extracting Numbers formula
A1 = 200 MB
=--LEFT(A1,LEN(A1)-3) Will return the numeric value 200 which can then be used in any number calculation. -- Biff Microsoft Excel MVP "JICDB" wrote in message ... 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 |
Help Extracting Numbers formula
At first it didn't work because I didn't put the two dashes in the beginning.
That's so cool - it works great! What do the two dashes offically do? "T. Valko" wrote: A1 = 200 MB =--LEFT(A1,LEN(A1)-3) Will return the numeric value 200 which can then be used in any number calculation. -- Biff Microsoft Excel MVP "JICDB" wrote in message ... 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 |
Help Extracting Numbers formula
The 2 dashes are called double negation and is one way of coercing a text
number into a numeric number. "200" is the text number (data type: text) The first dash coerces the text number into the numeric number -200. The 2nd dash then converts the -200 to positive 200. This also works on numeric numbers. Try entering these values in some cells: A1 = 200 A2 = -200 A3 = '200 The first thing you'll notice is A1 and A2 are aligned right which is one way to tell that they're numeric numbers (data type: numeric). A3 is aligned left which is one way to tell that it's a text number (data type: text). Enter these formulas in: B1: =-A1 C1: =-B1 Copy down to B3:C3 Double negation is also one method of coercing the Boolean logicals TRUE and FALSE to numeric 1 and 0 which can then be used in various ways for calculations. A4 = TRUE A5 = FALSE B4: =--A4 B5: =--A5 -- Biff Microsoft Excel MVP "JICDB" wrote in message ... At first it didn't work because I didn't put the two dashes in the beginning. That's so cool - it works great! What do the two dashes offically do? "T. Valko" wrote: A1 = 200 MB =--LEFT(A1,LEN(A1)-3) Will return the numeric value 200 which can then be used in any number calculation. -- Biff Microsoft Excel MVP "JICDB" wrote in message ... 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 |
Help Extracting Numbers formula
Wow - that is cool. I can see how I could use this in a formula for
true/false too. Thanks so much for your help. I'm going to have to document this so I can find it when I need it. "T. Valko" wrote: The 2 dashes are called double negation and is one way of coercing a text number into a numeric number. "200" is the text number (data type: text) The first dash coerces the text number into the numeric number -200. The 2nd dash then converts the -200 to positive 200. This also works on numeric numbers. Try entering these values in some cells: A1 = 200 A2 = -200 A3 = '200 The first thing you'll notice is A1 and A2 are aligned right which is one way to tell that they're numeric numbers (data type: numeric). A3 is aligned left which is one way to tell that it's a text number (data type: text). Enter these formulas in: B1: =-A1 C1: =-B1 Copy down to B3:C3 Double negation is also one method of coercing the Boolean logicals TRUE and FALSE to numeric 1 and 0 which can then be used in various ways for calculations. A4 = TRUE A5 = FALSE B4: =--A4 B5: =--A5 -- Biff Microsoft Excel MVP "JICDB" wrote in message ... At first it didn't work because I didn't put the two dashes in the beginning. That's so cool - it works great! What do the two dashes offically do? "T. Valko" wrote: A1 = 200 MB =--LEFT(A1,LEN(A1)-3) Will return the numeric value 200 which can then be used in any number calculation. -- Biff Microsoft Excel MVP "JICDB" wrote in message ... 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 |
Help Extracting Numbers formula
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "JICDB" wrote in message ... Wow - that is cool. I can see how I could use this in a formula for true/false too. Thanks so much for your help. I'm going to have to document this so I can find it when I need it. "T. Valko" wrote: The 2 dashes are called double negation and is one way of coercing a text number into a numeric number. "200" is the text number (data type: text) The first dash coerces the text number into the numeric number -200. The 2nd dash then converts the -200 to positive 200. This also works on numeric numbers. Try entering these values in some cells: A1 = 200 A2 = -200 A3 = '200 The first thing you'll notice is A1 and A2 are aligned right which is one way to tell that they're numeric numbers (data type: numeric). A3 is aligned left which is one way to tell that it's a text number (data type: text). Enter these formulas in: B1: =-A1 C1: =-B1 Copy down to B3:C3 Double negation is also one method of coercing the Boolean logicals TRUE and FALSE to numeric 1 and 0 which can then be used in various ways for calculations. A4 = TRUE A5 = FALSE B4: =--A4 B5: =--A5 -- Biff Microsoft Excel MVP "JICDB" wrote in message ... At first it didn't work because I didn't put the two dashes in the beginning. That's so cool - it works great! What do the two dashes offically do? "T. Valko" wrote: A1 = 200 MB =--LEFT(A1,LEN(A1)-3) Will return the numeric value 200 which can then be used in any number calculation. -- Biff Microsoft Excel MVP "JICDB" wrote in message ... 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 |
| All times are GMT +1. The time now is 09:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com