Formula not working

Aspose.Cells Product Family
1

Hi.

I have a formula which works in Excel, where I’m extracting text from a cell up to the first numeric digit.

For example cell B1 contains the string “APPLE000211”. I need to get the text up to the first numeric digit.

I found this formula =LEFT(B1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B1&"0123456789"))-1)

This works great in Excel, however with Aspose.Cells it returns #VALUE!

Any help will be appreciated

Thanks

2

Hi,


I have tried the following code and it works fine.

Workbook workbook = new Workbook("E://Test.xlsx");

Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
cells["C1"].Formula = "=LEFT(B1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B1&0123456789))-1)";
workbook.CalculateFormula();
workbook.Save("E://output.xlsx");

Please try this latest fix: Aspose.Cells for .NET v7.2.1.9
3

Thanks for your quick reply.

You are right that when I open it in excel it works, however the problem is from within Aspose.Cells when I check the cell value (cells[“C1”].Value) it returns #VALUE!.

I need to have the resulting value by checking the cells[“C1”].Value

Thanks for your help

4

Hi,

Thanks for your input.

I was able to replicate the problem using the code below. Please see the output at the end of this post.

We have logged this issue in our database. We will look into this issue and once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-40749.

C#


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.Worksheets[0];


worksheet.Cells[“B1”].PutValue(“Test001”);


Cell c1 = worksheet.Cells[“C1”];


c1.Formula = “=LEFT(B1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B1&0123456789))-1)”;


workbook.CalculateFormula();


Debug.WriteLine(c1.StringValue);


Output:
#VALUE!
5

The issues you have found earlier (filed as CELLSNET-40749) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.
6

Great,

however when trying to use the new update I get a license error:

The subscription included in this license allows free upgrades until 16 Mar 2012, but this version of the product was released on 14 Jun 2012. Please renew the subscription or use a previous version of the product.

7

Hi,

Thanks for your posting and using Aspose.Cells.

Your license has expired, you need to purchase a new license.

Please contact Aspose.Purchase for further information.