Is there a formula that adds, subtracts, multiplies, and or divides two digit numbers in a single cell?

[MrsFrankieH]

Are there formulas that will add, subtract, multiply, and/or divide data from two-digit numbers in a single cell?

I appreciate any help I can get. Thank you in advance.

 

[Eric W]

First, how are the numbers stored in F1:J1? In order to get a leading 0, you can either store the number as a number and use a custom format, or you can store the number as text. Depending on the answer, the formulas could be different.

Next, how are you computing subtraction? It appears that it's the bigger number minus the smaller number, with 0s being treated as a 10.

And division? Big number divided by small number? Any 0 gives a 0 answer?

 

[Peter_SSs]

First, how are the numbers stored in F1:J1? ... either ... a number and use a custom format, or you can store the number as text.

I'm assuming "Number Stored as Text", based on the little triangles at the top left of those cells in the image. Based on that, my suggestions are:

Excel Workbook
	E	F	G	H	I	J
1		03	09	20	36	39
2
3	Addition	3	9	2	9	2
4	Subtraction	7	1	8	3	6
5	Multiplication				8	7
6	Division				2	3
2 digit operations

 

[lrobbo314]

Here is a UDF version. The first argument is the cell with the number in it, and the second argument is the operation you want to perform. e.g. =TWODIGIT(A1,"+")

Function TWODIGIT(num As String, op As String)
Dim SP As Variant: SP = Split(StrConv(num, vbUnicode), Chr(0))

TWODIGIT = Evaluate(SP(0) & op & SP(1))

End Function

There is still the question on division as to which number is the numerator and which is the denominator. This code assumes that the first number is the numerator.

 

[Peter_SSs]

Here is a UDF version.

That doesn't produce the same output as the OP gave for any of the 4 rows.

 

[lrobbo314]

@Peter_SSs I see. Browser blocked the OP image. Was just going off of the description.

 

[lrobbo314]

Not as streamlined as the last, but this UDF replicates the results in the OP.

Function TWOII(num As String, op As String) As Variant
On Error GoTo ERH:
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")
Dim res As Double
Dim ret As Variant

AL.Add Right(num, 1)
AL.Add Left(num, 1)

res = Evaluate(Join(Array(AL(0), AL(1)), op))

If (AL(1) = "0" Or AL(0) = "0") And op = "-" Then
    If AL(1) = "0" Then
        TWOII = 10 - AL(0)
    Else
        TWOII = 10 - AL(1)
    End If
    Exit Function
End If

If res > 9 Then
    ret = Right(res, 1)
Else
    ret = res
End If

If ret = 0 Then TWOII = vbNullString Else TWOII = CDbl(ret)

Exit Function

ERH:

If Err.Number = 13 Then
    TWOII = vbNullString
Else
    MsgBox Error$
End If

End Function

 

[MrsFrankieH]

Hello again Eric!! Thanks for your response!
The numbers aren't stored per se, I would either input them of copy and paste them from another source. The way I'm computer subtraction is large to small because I don't want a negative. Yes, "0"s are treated as 10 and for division, as long as the answer is whole, any formula will do. If there's a division where the number has a "." in it (i.e. 3.4 or something) that would show as 0.

Thank you so much for your help again!

 

[MrsFrankieH]

Thank you Peter and it's great to see you again! I'm at a loss when it comes to excel terminology but I'm learning still.
Thank you for the formulas, I'll keep you posted as to how it works out.
Thanks again!

 

[MrsFrankieH]

Thank you Irobbo314! I appreciate your help!
I'm just getting acquainted with codes so I will add your code to my repertoire to study and learn.
You're amazing! Thank you!!