Skip to content

Working with cells

Aside from working with structured data, it's also possible to work with individual Excel cells.

The Cells() method returns a list of cells in a range:

  • Cells() returns a list of cells in the current selection
  • Cells("A1:B2") returns a list of four cells in the active sheet (A1, A2, B1, B2)
  • Cells("Sheet2!A1:B2") same as above, but the cells belong to Sheet2
  • Cells("abc") returns a list of cells in a named range or a table called "abc"

Modifying cell values

In the following example, some cells do not have spaces between the first and last name of the person. I select the cells in Excel, and use the Regex.Replace() method to insert a space where a lowercase letter is immediately followed by an uppercase letter.

Cells().Do(cell => cell.Value = Regex.Replace(cell.Value, "([a-z])(A-Z)", "$1 $2"))

Individual cells + Regex

Modifying cell formatting

We can also format cells. Let's highlight all cells of the current selection that contain a value of type string:

Cells()
    .Where(cell=>cell.Value?.GetType() == typeof(string))
    .Do(cell => cell.Interior.ColorIndex = 40)

Cell coloring

Comments