0

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 – user0 Aug 20 '18 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. – jason Aug 20 '18 at 2:51
  • could you provide a copy/sample of your sheet? (it would surely fasten up things) – user0 Aug 20 '18 at 3:06
  • is the output always same? what would be the output of different string then 1,0,0,1 ? – user0 Aug 20 '18 at 3:17
2

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:

enter image description here

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)
)))
| improve this answer | |
  • 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. – jason Aug 20 '18 at 15:03
  • 1
    @jason I'm really sorry for my incomplete answer. I updated my answer. Could you please confirm it? – Tanaike Aug 20 '18 at 22:17
  • @user0 Thank you for editing. I could study from your edit. – Tanaike Aug 20 '18 at 22:18
  • 1
    exactly what I needed. And really elegant solution. Thank you. – jason Aug 21 '18 at 16:10
1

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

enter image description here

| improve this answer | |
  • 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 – jason Aug 20 '18 at 2:12
  • @jason well, your question is too broad. try to edit it and add more data/examples – user0 Aug 20 '18 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" – jason Aug 20 '18 at 4:28
  • @jason the last one from my answer is able to do everything you requested – user0 Aug 20 '18 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.