How to sum cell with any non numeric characters

[prajay]

FOR EXAMPLE
[TABLE="width: 500"]
<tbody>[TR]
[TD]1000W.
[/TD]
[TD]1000W

[/TD]
[TD]1000D.

[/TD]
[TD]1000E

[/TD]
[TD]4000

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

HOW TO SUM ABOVE CASE TO GET ANSWER 4000 WITHOUT REMOVING TEXT & DOT OR ANY NON ALPHANUMERIC CHARACTER

GIVE ME VBA CODE OR SIMPLE FORMULA ...

 

[jtakw]

I'd just go with mikerickson's UDF, as a formula solution may get a little messy...

 

[prajay]

You could use this UDF with a formula like

=SUM(FirstValue(A1:D1))

Function FirstValue(inRange As Range) As Variant
    Dim Result As Variant
    Dim i As Long, j As Long
    With inRange
        If .Cells.Count = 1 Then
            Result = Val(CStr(.Cells(1, 1).Value))
        Else
            Result = .Value
            For i = 1 To .Rows.Count
                For j = 1 To .Columns.Count
                    Result(i, j) = Val(CStr(Result(i, j)))
                Next j
            Next i
        End If
    End With
    FirstValue = Result
End Function

This approach will handle data that has varying lengths of leading numerals.

How to sum only with the specific character ?
for example ...
1000G. 1000M. 1000g 2000e. = i want to sum only cell having g or G = 2000

 

[Aladin Akyurek]

How to sum only with the specific character ?
for example ...
1000G. 1000M. 1000g 2000e. = i want to sum only cell having g or G = 2000

Control+shift+enter, not just enter:

=SUM(IF(1-ISNUMBER(A2:D2),IF(ISNUMBER(SEARCH("G",A2:D2)),LEFT(A2:D2,SEARCH("G",A2:D2)-1)+0)))

 

[prajay]

Control+shift+enter, not just enter:

=SUM(IF(1-ISNUMBER(A2:D2),IF(ISNUMBER(SEARCH("G",A2:D2)),LEFT(A2:D2,SEARCH("G",A2:D2)-1)+0)))

Thank you its working perfect.... great...

Now tell me how to count cell that contains specific characters

for example

1000d. 2000f 3000g 4000d 3000d. =count D answer 3

 

[Aladin Akyurek]

Thank you its working perfect.... great...

Now tell me how to count cell that contains specific characters

for example

1000d. 2000f 3000g 4000d 3000d. =count D answer 3

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(SEARCH("D",A2:D2)),1))

or just enter:

=COUNTIFS(A2:D2,"*"&"D"&"*")

"D" can be replaced by a cell address like J2 housing D as the search item.

 

[prajay]

You could use this UDF with a formula like

=SUM(FirstValue(A1:D1))

Function FirstValue(inRange As Range) As Variant
    Dim Result As Variant
    Dim i As Long, j As Long
    With inRange
        If .Cells.Count = 1 Then
            Result = Val(CStr(.Cells(1, 1).Value))
        Else
            Result = .Value
            For i = 1 To .Rows.Count
                For j = 1 To .Columns.Count
                    Result(i, j) = Val(CStr(Result(i, j)))
                Next j
            Next i
        End If
    End With
    FirstValue = Result
End Function

This approach will handle data that has varying lengths of leading numerals.

if there is any single alphabet alone in cell then it gives error value of #NAME ?

for example
1000d. 5000w. 3000e H 2000r =#NAME ? because of only alphabet present in cell

 

[Rick Rothstein]

if there is any single alphabet alone in cell then it gives error value of #NAME ?

for example
1000d. 5000w. 3000e H 2000r =#NAME ? because of only alphabet present in cell

I think the following UDF will give you the values you want...

Function SumRange(Rng As Range) As Double
  Dim Cell As Range
  For Each Cell In Rng
    SumRange = SumRange + Val(Cell.Value)
  Next
End Function

To use it, simply put this in the cell...

=SumRange(A1:D1)

where you would change the range as needed.