software

I want to be able to add a column with mixed numerals. For example: The
sum
of A2:A34 with the values in the cells being 1B or 1F or so.
What would the formula be?--
excel303
------------------------------------------------------------------------
excel303's Profile: www.excelforum.com/member.php...oamp;userid=31816
View this thread: www.excelforum.com/showthread...hreadid=515440ASAP Utilities, a free add-in available at www.asap-utilities.com has a
feature that will remove all alpha-characters from cells, leaving only the
quot;numberquot; parts.......

hth
Vaya con Dios,
Chuck, CABGx3
quot;excel303quot; wrote:

gt;
gt; I want to be able to add a column with mixed numerals. For example: The
gt; sum
gt; of A2:A34 with the values in the cells being 1B or 1F or so.
gt; What would the formula be?
gt;
gt;
gt; --
gt; excel303
gt; ------------------------------------------------------------------------
gt; excel303's Profile: www.excelforum.com/member.php...oamp;userid=31816
gt; View this thread: www.excelforum.com/showthread...hreadid=515440
gt;
gt;


That works, but, I need to keep the values in the cells, only add the
numbers. ASAP Utilities is awesome by the way.--
excel303
------------------------------------------------------------------------
excel303's Profile: www.excelforum.com/member.php...oamp;userid=31816
View this thread: www.excelforum.com/showthread...hreadid=515440Copy your data over to a helper column and run the routine on that
column.....this will keep your original data intact........

Vaya con Dios,
Chuck, CABGx3
quot;excel303quot; wrote:

gt;
gt; That works, but, I need to keep the values in the cells, only add the
gt; numbers. ASAP Utilities is awesome by the way.
gt;
gt;
gt; --
gt; excel303
gt; ------------------------------------------------------------------------
gt; excel303's Profile: www.excelforum.com/member.php...oamp;userid=31816
gt; View this thread: www.excelforum.com/showthread...hreadid=515440
gt;
gt;


I'm using the following formula with a helper column, now I'm not able
to add the helper column. =LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1amp;quot; 0123456789quot;)),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9},quot;01 23456789quot;amp;A1))),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT(quot;1:quot;amp;(LEN(A1)))),1))))--
excel303
------------------------------------------------------------------------
excel303's Profile: www.excelforum.com/member.php...oamp;userid=31816
View this thread: www.excelforum.com/showthread...hreadid=515440I don't understand why you are using the formula.........I thought you just
had mixed text and numerical values in column A and wanted to just add the
numerical parts...............be that the case, then just copy and
paste-special-values your column A over to a helper column and then use ASAP
Utilities to delete all the alpha-characters in that helper column then add
the numbers that are left.....

Vaya con Dios,
Chuck, CABGx3
quot;excel303quot; wrote:

gt;
gt; I'm using the following formula with a helper column, now I'm not able
gt; to add the helper column.
gt;
gt;
gt; =LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1amp;quot; 0123456789quot;)),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9},quot;01 23456789quot;amp;A1))),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT(quot;1:quot;amp;(LEN(A1)))),1))))
gt;
gt;
gt; --
gt; excel303
gt; ------------------------------------------------------------------------
gt; excel303's Profile: www.excelforum.com/member.php...oamp;userid=31816
gt; View this thread: www.excelforum.com/showthread...hreadid=515440
gt;
gt;


I apologize for the confusion, I'm creating a worksheet for other users
to input. I have a column with mixed numerals: ie 1b or 1f or
2b....Ijust need to add the numbers automaticaly, but still keep the
letters present.
Thank you so much for your helpCLR Wrote:
gt; I don't understand why you are using the formula.........I thought you
gt; just
gt; had mixed text and numerical values in column A and wanted to just add
gt; the
gt; numerical parts...............be that the case, then just copy and
gt; paste-special-values your column A over to a helper column and then use
gt; ASAP
gt; Utilities to delete all the alpha-characters in that helper column then
gt; add
gt; the numbers that are left.....
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3--
excel303
------------------------------------------------------------------------
excel303's Profile: www.excelforum.com/member.php...oamp;userid=31816
View this thread: www.excelforum.com/showthread...hreadid=515440Are you wanting to quot;countquot; the 1b's and the 1f's and the 2b's, etc?

Then try
=COUNTIF(A:A,quot;1bquot;)
=COUNTIF(A:A,quot;1fquot;)
=COUNTIF(A:A,quot;2bquot;)

If you are wanting to sum the 1's and 2's, then try my suggestion in my last
post.

Post back if you're still having trouble.......maybe give some specific cell
values and what results you expect.

Vaya con Dios,
Chuck, CABGx3quot;excel303quot; wrote:

gt;
gt; I apologize for the confusion, I'm creating a worksheet for other users
gt; to input. I have a column with mixed numerals: ie 1b or 1f or
gt; 2b....Ijust need to add the numbers automaticaly, but still keep the
gt; letters present.
gt; Thank you so much for your help
gt;
gt;
gt; CLR Wrote:
gt; gt; I don't understand why you are using the formula.........I thought you
gt; gt; just
gt; gt; had mixed text and numerical values in column A and wanted to just add
gt; gt; the
gt; gt; numerical parts...............be that the case, then just copy and
gt; gt; paste-special-values your column A over to a helper column and then use
gt; gt; ASAP
gt; gt; Utilities to delete all the alpha-characters in that helper column then
gt; gt; add
gt; gt; the numbers that are left.....
gt; gt;
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt;
gt;
gt; --
gt; excel303
gt; ------------------------------------------------------------------------
gt; excel303's Profile: www.excelforum.com/member.php...oamp;userid=31816
gt; View this thread: www.excelforum.com/showthread...hreadid=515440
gt;
gt;


Ok, my spreadsheet is used to track different types of information, I'm
good with the rest of it. The one column I need help with is from
U4:U27. in this column the user will input a letter(F or B) followed by
the quantity of each. Is there a way to sum the numbers only and still
have the original value showing in the cell the user typed in?

1F
1b
Total: 2--
excel303
------------------------------------------------------------------------
excel303's Profile: www.excelforum.com/member.php...oamp;userid=31816
View this thread: www.excelforum.com/showthread...hreadid=515440If you want to use a helper column, then you can change each value into a
number and sum that column using this...copied down from U4 to U27

=IF(LEN(U4)gt;0,MID(U4,2,99)*1,quot;quot;)

The only way I know to sum them without using a helper column would be a
humongus formula, like this......

=SUM(MID(U4,2,99*1),MID(U5,2,99)*1,MID(U6,2,99)*1. ....etc etc to U27)

hth
Vaya con Dios,
Chuck, CABGx3
quot;excel303quot; gt; wrote in
message ...
gt;
gt; Ok, my spreadsheet is used to track different types of information, I'm
gt; good with the rest of it. The one column I need help with is from
gt; U4:U27. in this column the user will input a letter(F or B) followed by
gt; the quantity of each. Is there a way to sum the numbers only and still
gt; have the original value showing in the cell the user typed in?
gt;
gt; 1F
gt; 1b
gt; Total: 2
gt;
gt;
gt; --
gt; excel303
gt; ------------------------------------------------------------------------
gt; excel303's Profile:
www.excelforum.com/member.php...oamp;userid=31816
gt; View this thread: www.excelforum.com/showthread...hreadid=515440
gt;