formula help

[excel303]

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?

[CLR]

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

[excel303]

That works, but, I need to keep the values in the cells, only add the numbers. ASAP Utilities is awesome by the way.

[CLR]

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

[excel303]

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},"0123456789"&A1))),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1))))

[CLR]

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},"0123456789"&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
>
>