computing numeric with text

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

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