Adding cells containing asterisks

Question

I have a number of cells I want to add up but some of them contains asterisks so excel just ignore those.for ex. 2, 10*, 10**, i want to say 22.How can I force excel to ignore those and just add the numeric values?

Answer 1

Hi,

Try this ARRAY formula and see below on how to enter it. Note it won't handle blank cells

=SUM(--(SUBSTITUTE(A1:A6,"*","")))

If there could be blanks, still an ARRAY formula

=SUM(IF(ISNUMBER(SEARCH("*",A1:A6)),SUBSTITUTE(A1:A6,"*","")+0))

This is an array formula which must be entered by pressing CTRL+Shift+Enter

and not just Enter. If you do it correctly then Excel will put curly brackets

around the formula {}. You can't type these yourself. If you edit the formula

you must enter it again with CTRL+Shift+Enter.

Answer 2

Hi,

Try this

=SUMPRODUCT(1*(SUBSTITUTE(D6:D8,"*","")))

Answer 3

Hi, can you eliminate the asterisks if yes press CTRL+H, find what enter *, replace with don't enter anything, replace all

I believe that Excel will interpret the asterisk as a wildcard and effectively erase everything. Excel's Find and Replace needs to have a tilde (e.g. ~) prefix as an escape character that tells the find and replace routine to treat ~* as a single asterisk and not a wildcard.

Answer 4

How can I force excel to ignore those and just add the numeric values?

There is a Microsoft Office article detailing this procedure (see below).

                   

The formula in C1 is,

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))

... which is an array formula and needs to be finalized with Ctrl+Shift+Enter↵ rather than just Enter↵.

The formula to SUM() the stripped numbers in C4 is,

=SUM(1*MID(A1:A3,MATCH(TRUE,ISNUMBER(1*MID(A1:A3,ROW($1:$9),1)),0),COUNT(1*MID(A1:A3,ROW($1:$9),1))))

... which is another array formula and needs to be finalized with Ctrl+Shift+Enter↵ rather than just Enter↵.

More information at,

Extracting numbers from alphanumeric strings (by Ashish Mathur)

Answer 5

Hi, can you eliminate the asterisks if yes press CTRL+H, find what enter *, replace with don't enter anything, replace all

if not two solutions have a formula like this howevever you will have to enter each cell to be add

=LOOKUP(10^10,MID(A1,1,ROW(INDIRECT("1:"&LEN(A1))))+0)+LOOKUP(10^10,MID(A2,1,ROW(INDIRECT("1:"&LEN(A2))))+0)

or you can have a UDF code to extract the numbers and then add that column

One method is with a User Defined Function (UDF):
============================
Option Explicit
Function Alpha(rg As Range) As String
Dim i As Integer
If rg.Count <> 1 Then
MsgBox ("Only a single cell allowed as argument")
Exit Function
End If
For i = 1 To Len(rg.Text)
Select Case Asc(Mid(rg.Text, i, 1))
Case 48 To 57, 65 To 90, 97 To 122
Alpha = Alpha & Mid(rg.Text, i, 1)
End Select
Next i
End Function
===========================
will strip out everything except 0-9,A-Z,a-z
To enter this UDF, <alt><F11> opens the Visual Basic Editor.
Ensure that your current workbook project is highlighted in the Project
Explorer, then Insert/Module and paste the above code into the window that
opens.
To use this function, enter =Alpha(A1) in a cell.