If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
computing numeric with text
Can you help me make a formula for column c without separating the text? Everyday were receiving report with this kind of formatting, it would be time consuming to separate a min. of 50 rows of this column. Column: A B C No. Part No. Qty 1 96642 1 piece 2 73342 2 pieces 3 33342 84 pieces 4 K4224 150 Pieces for your kind assistance. thanks and regards aboiy |
#2
|
|||
|
|||
computing numeric with text
Try
Data/Text to Columns. Select Delimited and click Next. Check the Space checkbox and click Finish. In article , "aboiy" wrote: Can you help me make a formula for column c without separating the text? Everyday were receiving report with this kind of formatting, it would be time consuming to separate a min. of 50 rows of this column. Column: A B C No. Part No. Qty 1 96642 1 piece 2 73342 2 pieces 3 33342 84 pieces 4 K4224 150 Pieces for your kind assistance. thanks and regards aboiy |
#3
|
|||
|
|||
computing numeric with text
aboiy
I notice there is a space between 1 piece, 2 pieces etc. Select column C and DataText to ColumnsDelimited by spaceFinish Not too time consuming. The entire column C will be split in one go. Make sure you insert a blank column to the right of Column C prior to the operation. Gord Dibben Excel MVP On Sat, 1 May 2004 08:15:18 -0700, "aboiy" wrote: Can you help me make a formula for column c without separating the text? Everyday were receiving report with this kind of formatting, it would be time consuming to separate a min. of 50 rows of this column. Column: A B C No. Part No. Qty 1 96642 1 piece 2 73342 2 pieces 3 33342 84 pieces 4 K4224 150 Pieces for your kind assistance. thanks and regards aboiy |
#4
|
|||
|
|||
computing numeric with text
JE & Gord, Thanks for your help, but can i rephrase my question, i think i made a mistake in the way i present my problem. I need to put a formula to "total" column c reflecting the results under, without adding any helper column or separating the text from its numeric data. Column: A B C No. Part No. Qty 1 96642 1 piece 2 73342 2 pieces 3 33342 84 pieces 4 K4224 150 Pieces TOTAL : 237 Can it be possible, someone told me it can be accomplished thru array but i dont know how to make use of it. Your kind assistance is requested. Thanks and regards. aboiy |
#5
|
|||
|
|||
computing numeric with text
On Sun, 2 May 2004 09:39:23 -0700, "aboiy" wrote:
JE & Gord, Thanks for your help, but can i rephrase my question, i think i made a mistake in the way i present my problem. I need to put a formula to "total" column c reflecting the results under, without adding any helper column or separating the text from its numeric data. Column: A B C No. Part No. Qty 1 96642 1 piece 2 73342 2 pieces 3 33342 84 pieces 4 K4224 150 Pieces TOTAL : 237 Can it be possible, someone told me it can be accomplished thru array but i dont know how to make use of it. Your kind assistance is requested. If the values for Qty (in column C) are always represented by a number, followed by space, followed by text, then the *array-entered* formula: =SUM(IF(NOT(ISERROR(FIND(" ",Qty))),--LEFT(Qty,FIND(" ",Qty)),"")) should do what you want. To *array-enter* a formula, hold down ctrlshift while hitting enter. --ron |
#6
|
|||
|
|||
computing numeric with text
On Sun, 02 May 2004 13:41:40 -0400, Ron Rosenfeld
wrote: On Sun, 2 May 2004 09:39:23 -0700, "aboiy" wrote: JE & Gord, Thanks for your help, but can i rephrase my question, i think i made a mistake in the way i present my problem. I need to put a formula to "total" column c reflecting the results under, without adding any helper column or separating the text from its numeric data. Column: A B C No. Part No. Qty 1 96642 1 piece 2 73342 2 pieces 3 33342 84 pieces 4 K4224 150 Pieces TOTAL : 237 Can it be possible, someone told me it can be accomplished thru array but i dont know how to make use of it. Your kind assistance is requested. If the values for Qty (in column C) are always represented by a number, followed by space, followed by text, then the *array-entered* formula: =SUM(IF(NOT(ISERROR(FIND(" ",Qty))),--LEFT(Qty,FIND(" ",Qty)),"")) should do what you want. To *array-enter* a formula, hold down ctrlshift while hitting enter. --ron And if you are certain there will be no empty entries, then: =SUM(--LEFT(Qty,FIND(" ",Qty))) (array-entered) --ron |
#7
|
|||
|
|||
computing numeric with text
Ron, It works! only the last formula that im having problem =SUM(--LEFT(Qty,FIND(" ",Qty))) im getting a "#value" result. Thank you for your help. aboiy -----Original Message----- On Sun, 02 May 2004 13:41:40 -0400, Ron Rosenfeld wrote: On Sun, 2 May 2004 09:39:23 -0700, "aboiy" wrote: JE & Gord, Thanks for your help, but can i rephrase my question, i think i made a mistake in the way i present my problem. I need to put a formula to "total" column c reflecting the results under, without adding any helper column or separating the text from its numeric data. Column: A B C No. Part No. Qty 1 96642 1 piece 2 73342 2 pieces 3 33342 84 pieces 4 K4224 150 Pieces TOTAL : 237 Can it be possible, someone told me it can be accomplished thru array but i dont know how to make use of it. Your kind assistance is requested. If the values for Qty (in column C) are always represented by a number, followed by space, followed by text, then the *array- entered* formula: =SUM(IF(NOT(ISERROR(FIND(" ",Qty))),--LEFT(Qty,FIND (" ",Qty)),"")) should do what you want. To *array-enter* a formula, hold down ctrlshift while hitting enter. --ron And if you are certain there will be no empty entries, then: =SUM(--LEFT(Qty,FIND(" ",Qty))) (array-entered) --ron . |
#8
|
|||
|
|||
computing numeric with text
Ron, It works! only the last formula that im having problem =SUM(--LEFT(Qty,FIND(" ",Qty))) im getting a "#value" result. Thank you for your help. aboiy -----Original Message----- On Sun, 02 May 2004 13:41:40 -0400, Ron Rosenfeld wrote: On Sun, 2 May 2004 09:39:23 -0700, "aboiy" wrote: JE & Gord, Thanks for your help, but can i rephrase my question, i think i made a mistake in the way i present my problem. I need to put a formula to "total" column c reflecting the results under, without adding any helper column or separating the text from its numeric data. Column: A B C No. Part No. Qty 1 96642 1 piece 2 73342 2 pieces 3 33342 84 pieces 4 K4224 150 Pieces TOTAL : 237 Can it be possible, someone told me it can be accomplished thru array but i dont know how to make use of it. Your kind assistance is requested. If the values for Qty (in column C) are always represented by a number, followed by space, followed by text, then the *array- entered* formula: =SUM(IF(NOT(ISERROR(FIND(" ",Qty))),--LEFT(Qty,FIND (" ",Qty)),"")) should do what you want. To *array-enter* a formula, hold down ctrlshift while hitting enter. --ron And if you are certain there will be no empty entries, then: =SUM(--LEFT(Qty,FIND(" ",Qty))) (array-entered) --ron . |
#9
|
|||
|
|||
computing numeric with text
"aboiy" wrote...
Can you help me make a formula for column c without separating the text? Everyday were receiving report with this kind of formatting, it would be time consuming to separate a min. of 50 rows of this column. Column: A B C No. Part No. Qty 1 96642 1 piece 2 73342 2 pieces 3 33342 84 pieces 4 K4224 150 Pieces ... Are you receiving these reports as text files or as Excel workbooks? If the former, then if you're already parsing the reports to use them in Excel, why not parse them to make quantity number and suffix separate columns? Better, if you're receiving these reports from other divisions within your organization or company, ask them to eliminate the suffix portion. However, the most robust approach would be =SUMPRODUCT(--LEFT(C2:C5,FIND(" ",C2:C5&" "))) If this results in errors, then it's likely you have some quantity fields that lack either an initial number or a space between the rightmost decimal digit in the initial number and the leftmost nonnumeral character in the remainder of the field. If that's the case, post back with real examples. -- To top-post is human, to bottom-post and snip is sublime. |
#10
|
|||
|
|||
computing numeric with text
On Mon, 3 May 2004 11:26:28 -0700, "aboiy" wrote:
Ron, It works! only the last formula that im having problem =SUM(--LEFT(Qty,FIND(" ",Qty))) im getting a "#value" result. Thank you for your help. aboiy Then use the first formula. You likely are referencing an empty cell with the second formula to get the error message. --ron |
|
Thread Tools | |
Display Modes | |
|
|