excel - Excell cell value is not read as Number? -

i trying add data in 2 cells of excel sheet if excel cell of type number not add cells. seems there space infornt of number not add....image below. there vba code remove space each of cell if presesnt.

i have exported excel pdf.

excel attempt convert value number if apply operator it, , conversion handle spaces. can use =a1*1 or a1+0 convert value in a1 number, or within function =sum(iferror(a1*1,0)).

that kind of implicit conversion automatically performs trim(). can conversion explicitly using funciton n(), or numbervalue() newer versions of excel. however, others have pointed out, many characters won't automatically handled , may need use substitute() remove them. instance, substitute(a1,160,"") non-breaking space, prime suspect because of prevalence in html. clean() function can give shortcut doing bunch of characters known problematic, it's not comprehensive , still need add own handling non-breaking space. can find ascii code specific characters grieving using code() function... instance code(mid(a1,1,1))

character handling udf

the udf below gives flexibility character handling approach allowing multiple characters removed every cell in range, , produces result can used argument. example, sum(removechar(a1:a5,160)) remove non-breaking spaces range being summed. multiple characters can removed being specified in either range or array, example sum(removechar(a1:a5,b1:b3)) or sum(removechar(a1:a5,{160,150})).

function removechar(r range, paramarray chval() variant)     dim x variant     dim resvals() variant     redim resvals(1 r.count)     'loop through range     j = 1 r.count         x = r(j).value2         if x <> empty             'try treating character argument array             'if fails, try treating range             on error resume next             = 1 ubound(chval(0))                 x = replace(x, chr(chval(0)(i)), "")             next             if err = 92                 err.clear                 each rng in chval(0)                     x = replace(x, chr(rng.value2), "")                 next             end if             err.raise (err)             on error goto 0             'if numeric convert number             'so numbers treated such             'when array passed argument             if isnumeric(x)                 resvals(j) = val(x)             else                 resvals(j) = x             end if         end if     next     'return array of type variant     removechar = resvals end function 

numeric verifying udf

the drawback replacing characters it's not comprehensive. if want that's more of catch-all, perhaps this.

function getnumvalues(r range)     dim c, temp string     dim numvals() double     redim numvals(1 r.count)     'loop through range     j = 1 r.count         'loop through characters         'allow initial short-circuit if numeric         = 1 len(r(j).value2)             c = mid(r(j).value2, i, 1)             'if character valid number include in temp string             if isnumeric(c) or c = application.decimalseparator or c = application.thousandsseparator                temp = temp + c             end if         next         'assign temp string array of type double         'use val() function convert string number         numvals(j) = val(temp)         'reset temp string         temp = empty     next     'return array of type double     getnumvalues = numvals end function