Search code examples
google-sheetssyntaxgoogle-sheets-formulaimportrange

value(importrange()) not converting text into number

Set-up

I'm importing the number value of a cell from one Google Sheet to another.

Issue

Using importrange("sheet_url","cell"), the value imports just fine. However, I can't perform any calculations with the imported value.

I thought importrange() imports the number value as a text, but value(importrange("sheet_url","cell")) which should handle texts gives a #VALUE error.

Question

How can I import the number value such that I can perform calculations with it?

Solution

  • try in Revenue tab, cell G85:

    =SUBSTITUTE(IMPORTRANGE("1G6_3i5rSEdskNX2SLTPnQURRldbKnUrGOfsTX36YHpE"; "Paid Revenue!B12");
 "."; ",")

    the reason is the transition from US syntax to EU syntax

    0.1 = valid number in US syntax
    0,1 = valid number in EU syntax