# sum text plus number

##### Well-known Member
hi all...

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

any help thanks...

m,.susanto

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### Rick Rothstein

##### MrExcel MVP
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)...
Code:
``````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.

##### Well-known Member
hi rick...thanks a lot...
could you make not in udf...i mean usually formula?

##### Well-known Member
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 1box 2
9
box 3box 4
 Sheet: Sheet1

#### Rick Rothstein

##### MrExcel MVP

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 1box 2
9
box 3box 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.

##### Well-known Member
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

Last edited:

#### Rick Rothstein

##### MrExcel MVP

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?

##### Well-known Member
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

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

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

#### Rick Rothstein

##### MrExcel MVP
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.

Last edited:

Replies
0
Views
40
Replies
1
Views
18
Replies
11
Views
80
Replies
1
Views
74
Replies
11
Views
37