computing numeric with text

  • Thread starter Thread starter aboiy
  • Start date Start date
A

aboiy

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

I notice there is a space between 1 piece, 2 pieces etc.

Select column C and Data>Text to Columns>Delimited by space>Finish

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
 
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
 
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.
Click to expand...

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 <ctrl><shift>
while hitting <enter>.


--ron
 
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 <ctrl><shift>
while hitting <enter>.


--ron
Click to expand...

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-----
Click to expand...
 
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-----
Click to expand...
 
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
Click to expand...
...

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.
 
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
Click to expand...

Then use the first formula.

You likely are referencing an empty cell with the second formula to get the
error message.


--ron
 
Harlan & Ron,

Thank you for your tips. Now everythings seems to be more
easy now than before, results comes so fast by just
putting your formula under these figures.

Regards,

aboiy
 
Back
Top