Correct Formula for This Combination

[Jasa P]

Good morning,

I'm trying to get a solution for my problem. I'm sure this is fairly simple, but I don't know how to resolve this combination. So, I need your help.

I have a combination of
20.10.10x5.15.25/
in cell A1

The problem are:
a. The result for combination before "x" should be the amount of dots (.) + 1, so the result is 3
b. The result for combination between "x" and "/" should be the sum of the numbers, so the result is 45

Then, the results from a and b must be multiplied, so the final result is 135 in cell B1

I'm using Excel 2007 and Windows XP

I'm sorry for my poor English.
Any help would be great. Thank you.

Best Regards

Jasa

 

[T. Valko]

Good morning,

I'm trying to get a solution for my problem. I'm sure this is fairly simple, but I don't know how to resolve this combination. So, I need your help.

I have a combination of
20.10.10x5.15.25/
in cell A1

The problem are:
a. The result for combination before "x" should be the amount of dots (.) + 1, so the result is 3
b. The result for combination between "x" and "/" should be the sum of the numbers, so the result is 45

Then, the results from a and b must be multiplied, so the final result is 135 in cell B1

I'm using Excel 2007 and Windows XP

I'm sorry for my poor English.
Any help would be great. Thank you.

Best Regards

Jasa

This works on the single example you posted. It's usually a good idea to post several examples so we can see if the data is consistent.

Array entered**:

=(LEN(LEFT(A2,SEARCH("x",A2)))-LEN(SUBSTITUTE(LEFT(A2,SEARCH("x",A2)),".",""))+1)*SUM(IF(MID("."&MID(A2,SEARCH("x",A2)+1,20),COLUMN(1:1),1)=".",--(0&MID(MID(A2,SEARCH("x",A2)+1,20),COLUMN(1:1),FIND(".",MID(SUBSTITUTE(A2,"/","."),SEARCH("x",A2)+1,20),COLUMN(1:1))-COLUMN(1:1)))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

As you can see, that formula is quite complicated and is calculation intensive. I think you'd be better off with a VBA user defined function. I'm not much of programmer so someone else will need to help you with that.

 

[Jasa P]

I got the result is Value. VBA user? So, Should I post new thread and use "VBA" for the title? thank you so much.

 

[T. Valko]

I got the result is Value. VBA user? So, Should I post new thread and use "VBA" for the title? thank you so much.

The formula worked for me:

Book1
	A	B
1	String	Result
2	20.10.10x5.15.25/	135
Sheet1

Did you enter the formula as an array formula?

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

As far as using a VBA solution, if after a period of time has passed and no one has replied with a VBA solution then you should start a new thread. A reasonable period of time would be about 1 day.

 

[chirp]

i found this pretty tricky to do quickly in vba...

split was the first thing i thought of using, split by "x" then count dots on left and then split by "." on the right, and sum the result.

this wasn't all that fast, and so i tried a byte array approach, which is my sort of goto method when dealing with strings. this function is the result of that approach, but is not much faster than the formula based approach that was supplied.

the overhead of vba udf calls makes it difficult to compete with even complex formulas.

this makes a bunch of assumptions about your data, and will cause problems if the supplied example is not consistent with the rest of your data.

Function funnySum(vStr As String) As Long
Dim bArr() As Byte, conCnt As Long
Dim dotCnt As Long, isLeft As Boolean

If vStr = vbNullString Then Exit Function
bArr = Left(vStr, InStr(1, vStr, "/") - 1)

For i = UBound(bArr) - 1 To 0 Step -2
    If isLeft Then
        If bArr(i) < 47 Then dotCnt = dotCnt + 1
    Else
        If bArr(i) > 48 Then
            If bArr(i) < 58 Then
                funnySum = funnySum + (bArr(i) - 48) * 10 ^ conCnt
                conCnt = conCnt + 1
            ElseIf bArr(i) = 120 Then
                isLeft = True
            End If
        Else
            conCnt = 0
        End If
    End If
Next

funnySum = funnySum * (dotCnt + 1)

End Function

 

[Jasa P]

The formula worked for me:

Sheet1

<table style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:136px;"><col style="width:72px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="text-align:center; border-style:solid; border-width:1px; border-color:#000000; ">String</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Result</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">20.10.10x5.15.25/</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">135</td></tr></tbody></table>


Did you enter the formula as an array formula?


As far as using a VBA solution, if after a period of time has passed and no one has replied with a VBA solution then you should start a new thread. A reasonable period of time would be about 1 day.

Hi, thank you so much. It works, thank u. Thank u for helping me

 

[chirp]

just for completeness there is a mistake in the vba method that 48 should be 47.

i would also probably go with the formula approach on this one!

 

[Jasa P]

i found this pretty tricky to do quickly in vba...

split was the first thing i thought of using, split by "x" then count dots on left and then split by "." on the right, and sum the result.

this wasn't all that fast, and so i tried a byte array approach, which is my sort of goto method when dealing with strings. this function is the result of that approach, but is not much faster than the formula based approach that was supplied.

the overhead of vba udf calls makes it difficult to compete with even complex formulas.

this makes a bunch of assumptions about your data, and will cause problems if the supplied example is not consistent with the rest of your data.

Function funnySum(vStr As String) As Long
Dim bArr() As Byte, conCnt As Long
Dim dotCnt As Long, isLeft As Boolean

If vStr = vbNullString Then Exit Function
bArr = Left(vStr, InStr(1, vStr, "/") - 1)

For i = UBound(bArr) - 1 To 0 Step -2
    If isLeft Then
        If bArr(i) < 47 Then dotCnt = dotCnt + 1
    Else
        If bArr(i) > 48 Then
            If bArr(i) < 58 Then
                funnySum = funnySum + (bArr(i) - 48) * 10 ^ conCnt
                conCnt = conCnt + 1
            ElseIf bArr(i) = 120 Then
                isLeft = True
            End If
        Else
            conCnt = 0
        End If
    End If
Next

funnySum = funnySum * (dotCnt + 1)

End Function

Sorry, but I'm confused . Can you tell me a general way, please. Thank you so much. Sorry for making difficult.

 

[Jasa P]

just for completeness there is a mistake in the vba method that 48 should be 47.

i would also probably go with the formula approach on this one!

Thank u for helping me. I'm sure it'll useful, I'm trying to understand the VBA code.

 

[T. Valko]

Hi, thank you so much. It works, thank u. Thank u for helping me

You're welcome. Thanks for the feedback!