### Remove special characters from excel cell using macro

Press Alt+F11 then Insert>Module :

```Function removeSpecialCharacters(sInput As String) As String

Dim s As String, temp As String, i As Long
Dim C As String

s = sInput
If s = "" Then Exit Function
temp = ""

For i = 1 To Len(s)
C = Mid(s, i, 1)
If AscW(C) > 31 And AscW(C) < 127 Then
temp = temp & C
End If
Next i

removeSpecialCharacters = temp
End Function
```

### If cell equals in Excel

`=IF(A1="red",true result,false result)`

### Get decimal part of a number in Excel

`=B6-TRUNC(B6)`

### Excel COUNTIF Function

```=COUNTIF(D5:D12,">100") // count sales over 100
=COUNTIF(B5:B12,"jim") // count name = "jim"
=COUNTIF(C5:C12,"ca") // count state = "ca"
=COUNTIF(A1:A10,100) // count cells equal to 100
=COUNTIF(A1:A10,"<"&B1) // count cells less than  B1```

### Check Cell contains specific text in Excel

`ISNUMBER(SEARCH(substring,text))`
`ISNUMBER(SEARCH(C5,B5))`

Case sensitive version

`ISNUMBER(FIND(substring,text))`

If cell contains

`IF(ISNUMBER(SEARCH(substring,text)), "Yes", "No")`

With hardcoded search string

`ISNUMBER(SEARCH("apple",A1))`

### Get value of a cell at position (row,column) with openpyxl

```from openpyxl import load_workbook

test_sheet = wb["Test"]
print(test_sheet.cell(None, 1, 1).value)```

### Check If cell is blank in Excel

```=IF(D5="","Open","Closed")
```
```=IF(ISBLANK(D5),"Open","Closed")
```

### Sort Numeric Columns in Excel using Formulas

`=RANK(C5,sales)+COUNTIF(\$C\$5:C5,C5)-1`

where “sales” is the named range C5:C11

### Convert text to number with VALUE formula in Excel

`=VALUE("\$1,000")`
```=VALUE(A2)
```
`=VALUE(RIGHT(A2,3))`

### Excel ROW Function

The Excel ROW function returns the row number for a reference. For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula.

```=ROW(A3) // returns 3
=ROW(D5) // returns 5
=ROW(F10:J15) // returns 10```