Replace character in excel cell

0
votes

I'm exporting some data in sql table to a excel file using c# code. After exporting the data to the excel I need to append single quote any invalid text in the column A. (like staring with =,+)

I've added excel formula to remove the characters

added excel formula as

=IF(OR(LEFT(A2,1)=\"= \",LEFT(A2,1)=\"+ \"),\"'\"&A2,A2)

After applying I need to get the same text by appending single quote to the start if it contains any invalid character. ('=xxx or '+yyy) but cell contents changing to the 0 when formula applied

1
c#excel-formula
It would be awesome if you could show us the C# code. - mjwills

1 Answers

0
votes

I have recreated my understanding of the problem in a dummy Excel spreadsheet (screen shot below).

Dummy Excel spreadsheet:

  • Cells A1, A4, and A7 are data samples. A1 is good data that should not be altered. A4 and A7 start with = and + respectively and should therefore be altered according to your requirements.
  • Cells in Column B have different formulas in them operating on the data sample cells. The exact formula used is displayed in its neighboring cell in Column C (i.e. B1's formula is displayed in C1, B2's formula is displayed in C2, etc.).

Excel spreadsheet with test data and formulas

Observations:

  1. The formula resulting in 0 might be the result of the formula being applied to an incorrect cell. In the example above, B3, B6, and B9 all display 0. Note how the formulas are pointing to empty cells A3, A6, and A9. If you are seeing 0's unexpectedly, double check that your formula is referencing the correct cells.

  2. You listed an example of the formula as =IF(OR(LEFT(A2,1)=\"= \",LEFT(A2,1)=\"+ \"),\"'\"&A2,A2) which is presumably taken from a C# String given the \" escape characters? In any case, there is an extra space after the = sign in \"= \" and after the + sign in \"+ \". The extra space will always cause the equalities to fail since the LEFT(...,1) functions are only returning 1 character. At a minimum, try altering your formula to remove those extra spaces as follows:

=IF(OR(LEFT(A2,1)=\"=\",LEFT(A2,1)=\"+\"),\"'\"&A2,A2)
  1. If none of these solutions are helpful, would you mind sharing a snippet of your Excel file with the formulas displayed in text as I did above? That might help us diagnose the issue more easily.