sum text plus number

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
1,743
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
hi all...

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

any help thanks...

m,.susanto
 

Some videos you may like

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
Joined
Apr 18, 2011
Messages
36,325
Office Version
  1. 2010
Platform
  1. Windows
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.
 

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
1,743
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
hi rick...thanks a lot...
could you make not in udf...i mean usually formula?
 

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
1,743
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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
Joined
Apr 18, 2011
Messages
36,325
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
 

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
1,743
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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
Joined
Apr 18, 2011
Messages
36,325
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jan 8, 2013
Messages
1,743
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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
Joined
Apr 24, 2018
Messages
273
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 18, 2011
Messages
36,325
Office Version
  1. 2010
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,109,250
Messages
5,527,633
Members
409,778
Latest member
MagalieD

This Week's Hot Topics

Top