sum text plus number

[muhammad susanto]

hi all...

how to sum data like this :
box 1 +1 = box 2,
box 2+1=box 3
etc...

any help thanks...

m,.susanto

 

[Rick Rothstein]

how to sum data like this :
box 1 +1 = box 2,
box 2+1=box 3
etc...

You could use this UDF (user defined function)...

Function BoxSum(S As String) As Double
  BoxSum = Evaluate("SUM(" & Mid(S, 4) & ")")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use BoxSum just like it was a built-in Excel function. For example,

=BoxSum(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[muhammad susanto]

hi rick...thanks a lot...
could you make not in udf...i mean usually formula?

 

[muhammad susanto]

clarify :
i want box 1 as parameter ...if i change box 1+1, the other box adjusting number
Excel 2013 64 bit

	G​	H​
8​	box 1	box 2
9​	box 3	box 4

Sheet: Sheet1

 

[Rick Rothstein]

clarify :
i want box 1 as parameter ...if i change box 1+1, the other box adjusting number
Excel 2013 64 bit

	G​	H​
8​	box 1	box 2
9​	box 3	box 4

Sheet: Sheet1

Are you saying you will have something like Box 2 in a cell and in another cell you will have the number you want to add to it? If so, will the text always be the word "Box" followed by a space? The more detail you provide for us, the better we will be able to help you. Remember, we know absolutely nothing about your data, how it's laid out nor what you want to do with it... the more you tell us about these things the quicker you will get the answer you seek.

 

[muhammad susanto]

hi Ricks...in box 1 as a clue...
i want to sequential number of box cause i use lookup..
number of box as lookup.
like this pict..

https://jmp.sh/1b2Pbgu

 

[Rick Rothstein]

hi Ricks...in box 1 as a clue...
i want to sequential number of box cause i use lookup..
number of box as lookup.
like this pict..

https://jmp.sh/1b2Pbgu

Well, that picture totally confuses me as to what you want. What process are you using that you think you need formulas in those yellow colored boxes? Why can't you simply type in the box number directly?

 

[muhammad susanto]

when i type or replace in "box 1" with "box 9" ----so the another box adjusting to be box 10, box 11, box 12
etc....

 

[Finalfight40]

If i understand this right, your box 2 formula would look something like this?

="box " & RIGHT(G8, LEN(G8)-SEARCH(" ", G8))+1

 

[Rick Rothstein]

when i type or replace in "box 1" with "box 9" ----so the another box adjusting to be box 10, box 11, box 12
etc....

You did not tell us what cells the box numbrers are in, so I assumed "Box 1" is in cell A2. Given that, put these formulas in the indicated cells...

C2: ="BOX"&1+MID(A2,4,999)

A11: ="BOX"&1+MID(A2,4,999)

C11: ="BOX"&1+MID(A11,4,999)

You can now copy cells A11:C11 and paste it every 11 rows down. Once done, changing cell A2 to a different box number will change all the rest.