Apply trim to a column in excel

Question

Hi,

My application is an excel addin . I want to apply Trim for a column in excel (delete trailing blanks) through C# code.

Is there anyway to do this.

Regards,

Divya

---

divya

Answer 1

I hope this helps

this.Range["a1", missing].Value2 = this.Range["a1", missing].Value2.ToString().Trim();

---

http://vsto.tistory.com

Answer 2

hi,

it will not work as

this.Range["a1", missing].Value2.ToString() returns "system.object[,]"

thanks

---

divya

Answer 3

Hello,

Did you try to use above code I posted?

Please, try to use below Addin Code. I think it'll work.

Globals.ThisAddIn.Application.ActiveCell.Value2 = Globals.ThisAddIn.Application.ActiveCell.Value2.ToString().Trim();

---

http://vsto.tistory.com

Answer 4

Thanks for the reply.

Yes, I tried your previous code, it will not work.

Globals.ThisAddIn.Application.ActiveCell.Value2 = Globals.ThisAddIn.Application.ActiveCell.Value2.ToString().Trim(); - this code will just trim contents of a single cell, not an entire column. I can do this method for each cell, but it is time consuming when the number of rows are high.

I want to know if there is any method to trim contents of an entire column, not cell by cell

 

---

divya

Answer 5

I hope this Helps

1.

      foreach(Excel.Range rng in this.Range["a1",missing].EntireColumn.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants, Excel.XlSpecialCellsValue.xlTextValues))
      {
        rng.Value2 = rng.Text.ToString().Trim();
      }

2.

this.Range["a1", missing].EntireColumn.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants, Excel.XlSpecialCellsValue.xlTextValues).Replace(" ", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByColumns, false, false, missing, missing);

 

---

http://vsto.tistory.com

Answer 6

In the first option, it will loop through each cell which is time consuming.

Second option will replace all the spaces in the column, not only trailing blanks.. for e.g. if the string is 'Hello World ', it will change to 'HelloWorld'

---

divya

Answer 7

I think you need to read on SpecialCells Option.

http://msdn.microsoft.com/ko-kr/library/microsoft.office.interop.excel.range.specialcells(office.11).aspx

1. number 1000 row, text 1000 row

----first foreach  0.48~~~~~ , second foreach 0.949~~~~

2. number 100 row, text 1000 row

----first foreach  0.49~~~~~ , second foreach 0.56~~~~

And your foreach code + test

 

Test in code

  private void button1_Click(object sender, EventArgs e)
  {
   //this.Range["a1", missing].EntireColumn.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants, Excel.XlSpecialCellsValue.xlTextValues).Replace(" ", "", Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByColumns, false, false, missing, missing);

   
   double time1 = DateTime.Now.Ticks;
   foreach (Excel.Range rng in this.Range["a1", missing].EntireColumn.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants, Excel.XlSpecialCellsValue.xlTextValues))
   {    
    rng.Value2 = rng.Text.ToString().Trim();
    
   }

   double time2 = DateTime.Now.Ticks;
   double totalTime = (time2 - time1) / 10000000;
   MessageBox.Show(totalTime.ToString());



   time1 = DateTime.Now.Ticks;
   foreach (Excel.Range rng in this.Range["a1", missing].EntireColumn.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants,missing))
   {
    rng.Value2 = rng.Text.ToString().Trim();    
   }
   time2 = DateTime.Now.Ticks;
   totalTime = (time2 - time1) / 10000000;

   MessageBox.Show(totalTime.ToString());


  }

---

http://vsto.tistory.com

Answer 8

This method uses  excel's function. I think it is faster way than using vsto and above link I posted is useful as well.

Hope this is helpful.

  this.Range["d:d", missing].Insert(Excel.XlInsertShiftDirection.xlShiftToRight, missing);
      this.Range["d1:d1000", missing].Formula = "=trim(c1)";
      this.Range["d1:d1000", missing].Copy(missing);
      this.Range["d1:d1000", missing].PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);

 

 

---

http://vsto.tistory.com