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: http://www.excelforum.com/member.php...o&userid=31816
View this thread: http://www.excelforum.com/showthread...hreadid=515440

ASAP 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
"number" parts.......

hth
Vaya con Dios,
Chuck, CABGx3



"excel303" wrote:


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: http://www.excelforum.com/member.php...o&userid=31816
View this thread: http://www.excelforum.com/showthread...hreadid=515440

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: http://www.excelforum.com/member.php...o&userid=31816
View this thread: http://www.excelforum.com/showthread...hreadid=515440

Copy 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



"excel303" wrote:


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: http://www.excelforum.com/member.php...o&userid=31816
View this thread: http://www.excelforum.com/showthread...hreadid=515440

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},A1&" 0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9},"01 23456789"&A1))),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1))))


--
excel303
------------------------------------------------------------------------
excel303's Profile: http://www.excelforum.com/member.php...o&userid=31816
View this thread: http://www.excelforum.com/showthread...hreadid=515440

I 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



"excel303" wrote:


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},A1&" 0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9},"01 23456789"&A1))),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1))))


--
excel303
------------------------------------------------------------------------
excel303's Profile: http://www.excelforum.com/member.php...o&userid=31816
View this thread: http://www.excelforum.com/showthread...hreadid=515440