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

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

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

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

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

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

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
.

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
.

"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.

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