Excel: Dividing comma separated single cell numbers

Question

I have a string of numbers in a single Excel cell separated by commas, e.g. A2 = "4,3,2,7". I want a formula to be able to divide those number by 2 in cell B2, e.g. B2 = "2,1,1,3" (odd numbers should round down rather than up)

The formula needs to be able to account for:
- smaller or larger string of numbers
- for both single and double digit numbers
- numbers that are even or odd
- no VBA
- formula can use multiple columns but not the delimited text to columns feature (due to cell location)

I was able to get a formula to sum the string of numbers in A2 (4,3,2,7 = 16) but not able to divide by 2. My formula to sum the string is below. Any help is appreciated, thanks!

{=SUM(IF(ISERR(VALUE(MID(A2,ROW($A$1:OFFSET($A$1,LEN(A2)-1,0‌)),1))),0,VALUE(MID(‌A2,ROW($A$1:OFFSET($‌A$1,LEN(A2)-1,0)),1)‌)))}

is there always 4 elements? I don't think it's doable for variable number of arguments without VBA — phuclv
This website is not such as "tell me code". First, attempt yourself, if you get stuck, write your formula/code, then ask help. — bzimor
You should write what you have tried so far and better specify your goal and problem. — quantummind
After some explorations, I decided that it is not possible to do it with single cell formula. You have two ways: using more columns to parse them or using VBA to create custom formula. — bzimor

bzimor bzimor · Accepted Answer · 2016-11-19T06:36:06

In your example, use this formula into cell B1:

=SUBSTITUTE(SUBSTITUTE(A1,",","000")/2,"000",",")

Then result will be like this:

 |    A    |    B    |
 +---------+---------+  
1| 4,2,6,8 | 2,1,3,4 |
2|

Excel: Dividing comma separated single cell numbers

2 Answers