TEXTBEFORE on .NET return error

Aspose.Cells Product Family
1

I created an Excel with a formula ‘=TEXTBEFORE(A2," ")’ in cell A3, then run the following code in .NET. It can create a new file but the cell A3 shows “#NAME?” with content becomes ‘=_xlfn.TEXTBEFORE(A2," ")’. The same Excel run on Office 365 returned “John” in cell A3

  • is this relate to the version of Office installed on the development machine, or it should not use the Office installed locally?
  • the Aspose.Cells is already the latest version 24.5

var workbook = new Workbook(“text_before.xlsx”);
workbook.Worksheets[0].Cells[“A2”].Value = “John Yeung”;

workbook.CalculateFormula();

workbook.Save(“text_before_new.xlsx”);

2

@hsyam
By creating sample files and testing them on the latest version v24.5, we can obtain the correct results. Please refer to the attachment. result.zip (13.6 KB)

The sample code as follows:

var workbook = new Workbook(filePath + "text_before.xlsx");
workbook.Worksheets[0].Cells["A2"].Value = "John Yeung";

workbook.CalculateFormula();
Console.WriteLine(workbook.Worksheets[0].Cells["A3"].Value);
workbook.Save(filePath + "out_net.xlsx");

The output result:

John

Also, my Office 365 version is Microsoft® Excel® for Microsoft 365 MSO (Version 2404 Build 16.0.17531.20152) 64-bit.

3

@hsyam

TEXTBEFORE is new function supported by office 365 only. When you create such one formula manually in office 365 and then save as template file, then open the file with lower version of ms excel, you will see the same result: formula is shown as “=_xlfn…” and calculated as “#NAME?”.