How to apply + and - to numbers in Google Sheets

How to apply a positive or negative to values from one column to another column? (see image: for example in the first gray column and first white column, I want "3,-2" to apply to "31-82" so that the result would automatically output in another column as "34-80").

enter image description here

One of the programmers I hired (who no longer works on the project) deleted tabs in Google Sheets without my knowledge which showed the entire spreadsheets values and instead added some type of formula (why he did this I have no idea since all the raw data needs to be imported into MySQL).

2 answers

  • answered 2019-12-15 00:34 player0

    try:

    =REGEXEXTRACT(A24, "(.+)-")+REGEXEXTRACT(B24, "(.+),")&"-"&
     REGEXEXTRACT(A24, "-(.+)")+REGEXEXTRACT(B24, ",(.+)")
    

    0

    for arrayformula do:

    =ARRAYFORMULA(REGEXEXTRACT(A2:A, "(.+)-")+REGEXEXTRACT(B2:B, "(.+),")&"-"&
                  REGEXEXTRACT(A2:A, "-(.+)")+REGEXEXTRACT(B2:B, ",(.+)"))
    

  • answered 2019-12-15 03:34 user11982798

    enter image description here

    Another way without regex:

    = arrayformula( query ({split(B2:B3,","),split(SUBSTITUTE(A2:A3,"-",",-"),",")},
                       "Select Col1+Col3 label Col1+Col3 ''") & 
                query ({split(B2:B3,","),split(SUBSTITUTE(A2:A3,"-",",-"),",")},
                       "Select Col2+Col4 label Col2+Col4 ''"))