Adding cell reference between ampersands inside formula string

Adding cell reference between ampersands inside formula string

Posted by: kim.dok on 30 May 2023, 12:58 pm EST

• 

  kim.dok

  • Post Options:
  • Link
    Copy

  Posted 30 May 2023, 12:58 pm EST - Updated 30 May 2023, 1:03 pm EST

  Hi, when I’m trying to add cell reference in a formula as part of a string like: =TEXTJOIN(“=”,TRUE,"sum ") next to the word “sum”, I have to write “&&” and put the cursor between the ampersand so I’ll be able to select cell reference.
  
  for example if I’ll try to click on a cell to add it’s reference to the formula in these situation:
  
  

  I’ll get the error in SpreadJS Designer:
  
  

  But if I’ll try to select cell in between the ampersands like this, it will work and the cell reference will be added to the formula
  
  

  In Excel for example we are able to select cell reference before closing the & part.
  
  

  can we have some guidance on how to achieve the required outcome?

  Thanks.

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 31 May 2023, 11:36 pm EST - Updated 31 May 2023, 11:41 pm EST

  Hello Kim,

  Please note that the correct syntax or format to specify references with texts in the TEXTVALUE() function is ‘=TEXTJOIN(“=”,TRUE,“sum “&E4&””)’. That is why, when the formula is ‘=TEXTJOIN(“=”,TRUE,“sum “”&”)’, it does not allow to select the cell or cell range and returns value ‘sum "&’. However, if the formula is ‘=TEXTJOIN(“=”,TRUE,“sum “”&”")’ in the formula box and you try to select cell(or ranges) after &, it opens up a warning popup since it expects another & to form a valid syntax.

  Please note that when you hit enter after selecting cell or cell range after & in formula
  
  ‘=TEXTJOIN(“=”,TRUE,“sum “”&A1”)’, the value is not referenced from cell and value ‘sum "&C3’ is returned. Also, when you hit enter after selecting range after & in formula ‘=TEXTJOIN(“=”,TRUE,“sum “”&A1"”)’, excel opens a popup to auto correct the entered formula as the entered formula syntax is not valid.

  We are sorry for the inconvenience cause. However, SpreadJS does not provide the auto correct feature. It is important to note that MS Excel and SpreadJS return the same result for the same formula string entered in the formula.

  Please refer to the attached gifs.

  SpreadJS Behavior:

  

  Excel Behavior:

  

  

  Kindly let me know if you still face any problems.

  Regards,

  Ankit