CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Sinbad
Sinbad

Reputation: 47

Formula errors in Google Sheets

I have a Google Sheets with the following formula:

=IFERROR(IF(AA3="","",MID((LEFT(AA3,LEN(AA3)-1)),FIND("|",AA3)+1,30)))

now this returns a number.

The weird thing is it works in 100% of the cases and returns the appropriate number, if however I try and calculate with this number in a few odd occasions the new formula fails, telling me the "number" is not a number.

Example:

(-121|102)  -121    102  -->the first is a combination which I then break apart into -121 and 102

Copy the formulas down and all work 100%.

I then carry on to process these numbers and on the odd occasion, the formula tells me the "102" is not a number.

Below a sample output:

    (-121|102)  -121    102     76.00
    (-238|139‎)  -238    139‎       #VALUE!
    (-297|52)   -297     52     395.73
    (-127|201)  -127    201     186.81
    (-186|149)  -186    149     336.89
    (-141|120)  -141    120     290.08
    (-106|97‎)   -106     97‎       #VALUE!
    (-238|139‎)  -238    139‎       #VALUE!
    (-297|52‎)   -297     52‎       #VALUE!
    (-160|221)  -160    221     290.06
    (-197|200)  -197    200     294.55
    (-238|139‎)  -238    139‎       #VALUE!
     (-19|10)    -19     10       #VALUE!
     (-21|22)    -21     22     323.83

Is there anything I can put in the formula that would force the output to be a number? 

=IFERROR(IF(AA3="","",MID((LEFT(AA3,LEN(AA3)-1)),FIND("|",AA3)+1,30)))

Upvotes: 1

Views: 800

Answers (1)

Anders Gustafsson
Anders Gustafsson

Reputation: 15981

The problem is caused by "invisible" non-numeric characters in some of your cells. If you list the character codes of one of the invalid numbers, for example line 2 in your sample output, you will see that there is a fourth invisible character with code 8206 at the end of the text. You can use this formula to verify this fact (provided the cell is at position AA2):

=CODE(MID(AA2,FIND("|",AA2)+4,1))

You should be able to use regular expression replacement to get rid of the non-numerical characters, like this:

=REGEXREPLACE(/* some text */,"[^0-9]","")

A valid formula for extracting the second numerical value from one text cell based on your original formula would thus be:

=IF(AA2="","",REGEXREPLACE(MID(LEFT(AA2,LEN(AA2)-1),
                               FIND("|",AA2)+1,30),"[^0-9]",""))

However, I would recommend a more stable formulation for identifying the second value, based on the positions of both | and ):

MID(AA2,FIND("|",AA2)+1,FIND(")",AA2)-FIND("|",AA2)-1)

yielding the following resulting formula for sufficiently extracting the second numerical value from the text cell:

=IF(AA2="","",REGEXREPLACE(MID(AA2,FIND("|",AA2)+1,
                               FIND(")",AA2)-FIND("|",AA2)-1),"[^0-9]",""))

Upvotes: 1

Related Questions