Try this:

Function SumValuesInCell(WrappedText As String) As Double
Dim Vals As Variant
Dim sum As Double, i As Long

WrappedText = Replace(WrappedText, vbCr, "")
Vals = Split(WrappedText, vbLf)
For i = 0 To UBound(Vals)
sum = sum + Val(Trim(Vals(i)))
Next i
SumValuesInCell = sum
End Function

Function SumAll(R As Range) As Double
Dim cl As Range
Dim sum As Double
For Each cl In R.Cells
sum = sum + SumValuesInCell(cl.Value)
Next cl
SumAll = sum
End Function

Enter this into a general code module. Then in your example,
=SumAll(A1:A4) used as a user-defined spreadsheet function (put say in
A5) will yield 3991 (as it should). It could also be called in another
macro via something like SumAll(Range("A1:A4"))

This code works in Windows. It will also work in MAC OS X and above. If
for some reason you are using Excel in Macs with an older operating
system this code won't work because of the way those systems handled
newlines.

Hope that helps

-John Coleman

bijan wrote:
I have a huge data that some of them contain wrap text cells with number value,
how I can sum this example correctly with macro?
A B
1 10
20
2 54
3 3365
18
4 524