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)

=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

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