# Updating a cell with fixed format without using code, formulas only

I have cell A1 = `1,0,0,1` cell B1=`,1,,-1`

I need a formula that would be the equivalent of `=update(A1,B1)`.
The numbers between the commas can be any number. I need B1 to replace data in A1 where there is a number.

Cell C1 output: `1,1,0,-1`

Note: A1 and B1 will always be in that format, 4 numbers separated by commas.

more examples:

A1 = `1,0,0,1` cell B1=`,1,,-1` --> `1,1,0,-1`

A1 = `10,0,0,1` cell B1=`,1,,-1` --> `10,1,0,-1`

A1 = `10,0,0,01` cell B1=`,1,,-1` --> `10,1,0,-1`

A1 = `10,0,-1,0` cell B1=`,1,,-1` --> `10,1,-1,-1`

A1 = `10,1,0,1` cell B1=`,0,,-1` --> `10,0,0,-1`

A1 = `10,0,0,01` cell B1=`-1,1,,` --> `-1,1,0,01`

General:
A1 = `a,b,c,d` B1 = `e,f,g,h` --> make C1 by replacing `a` with `e`, `b` with `f`... etc, if `e,f,g,h` are not blanks.

• "B1 to replace data in A1" - this is not possible and it would lead to #REF! circular dependency paradox Aug 20, 2018 at 2:43
• @user0. Sorry for being unclear... what about now? I think it's possible, just complicated. probably needs a `split` function and maybe `replace` and `if` along with `arrayformula` I just haven't figured it out yet. Someone really good could probably make use of the `regexreplace`. Aug 20, 2018 at 2:51
• could you provide a copy/sample of your sheet? (it would surely fasten up things) Aug 20, 2018 at 3:06
• is the output always same? what would be the output of different string then 1,0,0,1 ? Aug 20, 2018 at 3:17

### Sample formula:

For example, when "A1" and "B1" are `1,0,0,1` and `,1,,-1`, respectively. Please put this formula to "C1".

``````=JOIN(",",ARRAYFORMULA(IF(
ISBLANK(SPLIT(REGEXREPLACE(B1,"([-\d]+)","\'\$1"),",",true,false)),
SPLIT(REGEXREPLACE(A1,"([-\d]+)","\'\$1"),",",true,false),
SPLIT(REGEXREPLACE(B1,"([-\d]+)","\'\$1"),",",true,false)
)))
``````
• Add `'` before a number. By this, for example, `01` can be used as `01`.
• Split the string in the cell.
• Compare each element.
• If there is no value in "B1", the element of "A1" is used.
• If there is a value in "B1", the element of "B1" is used.
• Join each element as a string value.

### References:

If I misunderstand your question, I'm sorry.

## Edit :

If you want to use `01` as `1`, please use the following formula.

``````=JOIN(",",ARRAYFORMULA(IF(
ISBLANK(SPLIT(B1,",",true,false)),
SPLIT(A1,",",true,false),
SPLIT(B1,",",true,false)
)))
``````
• This is exactly what I was looking for. Can you edit the formula, so the last step is not `Join each element as a string value` but `Join as value`? I threw in `01` just to test the code. Now i realize it should be all values and not string. Aug 20, 2018 at 15:03
• @jason I'm really sorry for my incomplete answer. I updated my answer. Could you please confirm it? Aug 20, 2018 at 22:17
• @user0 Thank you for editing. I could study from your edit. Aug 20, 2018 at 22:18
• exactly what I needed. And really elegant solution. Thank you. Aug 21, 2018 at 16:10

there are a lot of ways how to get it. for example:

`=IFERROR(INDEX(SPLIT(A1;",";1;1);1;1)&",1,"&INDEX(SPLIT(A1;",";1;1);1;3)&RIGHT(B1;3);)`

other approaches:

``````=IFERROR(INDEX(SPLIT(A1;",";1;1);1;1)&","&
INDEX(SPLIT(B1;",";1;1);1;2)&","&
INDEX(SPLIT(A1;",";1;1);1;3)&
RIGHT(B1;3);)
``````

``````=IFERROR(INDEX(SPLIT(A1;",";1;1);1;1)&LEFT(B1;1)&
INDEX(SPLIT(B1;",";1;1);1;2)&LEFT(B1;1)&
INDEX(SPLIT(A1;",";1;1);1;3)&
RIGHT(B1;3);)
``````

``````=IFERROR(INDEX(SPLIT(A1;",";1;1);1;1)&LEFT(B1;1)&
INDEX(SPLIT(B1;",";1;1);1;2)&LEFT(B1;1)&
INDEX(SPLIT(A1;",";1;1);1;3)&
INDEX(SPLIT(B1;",";1;1);1;4);)
``````

``````=IFERROR(JOIN(","; IF(INDEX(SPLIT(B1;",";1;0);1;1)<>"";INDEX(SPLIT(B1;",";1;0);1;1);
INDEX(SPLIT(A1;",";1;0);1;1));
IF(INDEX(SPLIT(B1;",";1;0);1;2)<>"";INDEX(SPLIT(B1;",";1;0);1;2);
INDEX(SPLIT(A1;",";1;0);1;2));
IF(INDEX(SPLIT(B1;",";1;0);1;3)<>"";INDEX(SPLIT(B1;",";1;0);1;3);
INDEX(SPLIT(A1;",";1;0);1;3));
IF(INDEX(SPLIT(B1;",";1;0);1;4)<>"";INDEX(SPLIT(B1;",";1;0);1;4);
INDEX(SPLIT(A1;",";1;0);1;4)));)``````

# final formula:

``````=IFERROR(JOIN(",";
IF(INDEX(SPLIT(B1;",";1;0);1;1)<>"";INDEX(SPLIT("'"&SUBSTITUTE(B1;",";",'");",";1;0);1;1);
INDEX(SPLIT("'"&SUBSTITUTE(A1;",";",'");",";1;0);1;1));
IF(INDEX(SPLIT(B1;",";1;0);1;2)<>"";INDEX(SPLIT("'"&SUBSTITUTE(B1;",";",'");",";1;0);1;2);
INDEX(SPLIT("'"&SUBSTITUTE(A1;",";",'");",";1;0);1;2));
IF(INDEX(SPLIT(B1;",";1;0);1;3)<>"";INDEX(SPLIT("'"&SUBSTITUTE(B1;",";",'");",";1;0);1;3);
INDEX(SPLIT("'"&SUBSTITUTE(A1;",";",'");",";1;0);1;3));
IF(INDEX(SPLIT(B1;",";1;0);1;4)<>"";INDEX(SPLIT("'"&SUBSTITUTE(B1;",";",'");",";1;0);1;4);
INDEX(SPLIT("'"&SUBSTITUTE(A1;",";",'");",";1;0);1;4)));)``````

• i don't understand. any of the numbers between the commas could be any number. not sure how a `if` statement with specific strings would help Aug 20, 2018 at 2:12
• @jason well, your question is too broad. try to edit it and add more data/examples Aug 20, 2018 at 2:15
• I see strings in it... i can't go through all of the combinations, but it has to be flexible so any of the data can be "updated" Aug 20, 2018 at 4:28
• @jason the last one from my answer is able to do everything you requested Aug 20, 2018 at 12:19