15 digit restriction

[googleelgoog]

Hi all,
How can I use more than 15 digits in Mat. operations?

Thanks in advance.

 

[Seti]

While TommyGun is correct that you have the 15 digit limitation for numbers, you do not have this for text, the limit is 1024 or some such number. If you can live with treating your numbers as text, the following UDF will add your numbers up. I haven't got subtraction in the code, but it would probably be pretty easy to check for minus signs at the beginning of the string.

Book13
	B	C	D	E
1		Format
2	1234567890123456345	Text
3	1234567890123456	Text
4	1235802458013570000	Number
5	1235802458013579801	Text
Sheet1

And here is the UDF. It could probably be cleaned up a little, but it seems to work.

Function AddText(var1 As String, var2 As String)
Dim len1 As Long, len2 As Long, maxlength As Long, temp As Long
Dim temp1 As Integer, temp2 As Integer, temp3 As Integer
    len1 = Len(var1)
    len2 = Len(var2)
    maxlength = Application.WorksheetFunction.Max(len1, len2)
    AddText = ""
    temp3 = 0
    For i = 1 To maxlength
        If i<= len1 Then
            temp1 = Val(Mid(var1, len1 - i + 1, 1))
        Else
            temp1 = 0
        End If
        If i<= len2 Then
            temp2 = Val(Mid(var2, len2 - i + 1, 1))
        Else
            temp2 = 0
        End If
        temp = temp1 + temp2 + temp3
        If temp > 9 Then
            temp3 = 1
            temp = temp - 10
        Else
            temp3 = 0
        End If
        AddText = CStr(temp) & AddText
    Next i
End Function

 

[googleelgoog]

You da man Seti.

Thanks a lot.

 

[Seti]

I started to work on subtraction and it is not as easy as I remembered. I need to worry about borrowing and when I have to borrow and the number I borrow from is a 0, I need to keep checking until I find a non 0 number. It is quite messy now so I will have to take some time to play around with it. I might not get to it until next week.