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

Question

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.

Answer 1

How about this sample formula? I think that there are several answers to your situation. So please think of this as one of them.

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.

Result:

References:

• SPLIT
• REGEXREPLACE
• JOIN

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)
)))

Answer 2

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)));)