Google-sheets – Updating a cell with fixed format without using code, formulas only

google sheets

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.

Best Answer

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:

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

Related Solutions

Google Sheets – Prevent Unwanted Rounding of Numbers

The solution is quite simple. Press this button to increase the number of decimals: enter image description here

Now it will show the correct amount of decimals:
enter image description here

Please be aware that this works only up to 15 digits. Beyond that, Google Spreadsheets will ignore the rest.

Google Sheets – SUM Row of Numbers After Extracting with REGEX

Use regexextract like

=arrayformula(sum(0+iferror(regexextract(A1:A3, "[\d\,\.]+"))))

How this works:

Regular expression extracts the first group of characters that consists of digits 0-9 commas or dots (such as 12,345.67, or 1.23, or just 1). This is a basic number match, you may need a stricter number regular expression.
The wrapper iferror is needed in case there is no such group (maybe the cell is empty), because regexextract has the annoying habit of showing #N/A instead of just giving an empty string.
Adding 0 forces Sheets to treat the result as a number.
Arrayformula and sum perform the summation over a range after running the formula.

A slightly shorter alternative is regexreplace:

=arrayformula(sum(0+regexreplace(A1:A3, "[^\d\,\.]", "")))

Here, regexreplace removes everything that is not a digit or period. An advantage is that there can be no error thrown. A disadvantage is that there is a greater chance of getting wrong results if, say, the third column has some digits 0-9 in it. Those digits would get appended to the number you want.