Search code examples
excelgoogle-sheetsexcel-formula

How to perform calculations on numbers stored as text in google sheets or excel?

enter image description here

Here numbers are stored as a list as text. There can be 1 to 20 numbers in the list. Need to minus 1st number in list A with 1st number in list B and store it List C as a text.

Get list of difference of numbers

Solution

  • Try using MAP() function in Excel

    enter image description here

    • Formula used in cell G2

    =MAP(C2,E2,LAMBDA(a,b,
 LET(c, TEXTSPLIT(a,,CHAR(10)), 
     d, TEXTSPLIT(b,,CHAR(10)), 
     TEXTJOIN(CHAR(10),,c-d))))

    Don't forget to wrap the cells by hitting ALT+H+W

    In Google Sheets:

    enter image description here

    =MAP(C2,E2,LAMBDA(a,b,
 LET(c, SPLIT(a,CHAR(10)), 
 d, SPLIT(b,CHAR(10)), 
 ARRAYFORMULA(JOIN(CHAR(10),c-d)))))

    Edit: LAMBDA() helper function is not needed:

    =TEXTJOIN(CHAR(10),,TEXTSPLIT(C2,,CHAR(10))-TEXTSPLIT(E2,,CHAR(10)))

    In Google Sheets also LAMBDA() helper is not needed:

    =JOIN(CHAR(10),ARRAYFORMULA(SPLIT(C2,CHAR(10))-SPLIT(E2,CHAR(10))))