Skip to main content
6 of 6
added 5 characters in body; edited title
serenesat
  • 10k
  • 30
  • 33
  • 50

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.

jason
  • 1.1k
  • 5
  • 23
  • 41