expertatexcel.com

Excel Special Characters

Special Characters refer to the keys on your keyboards that aren't letters, numbers, space or control keys.

!@#$%^&*()_=- are examples of special characters. Most of the special characters are above the numbered keys on the keyboard and accessed by pressing the Shift key

In Excel we will first discuss the $ character and then go on to different ones

$

Known as the Dollar sign it is used to represent money, especially money in US Dollars. See Currencies for more information.

The usage of the $:

Declaring a string variable, ex. name$

Formatting a number to money, ex. "$#,

0.00"

Declaring a function to return a string, ex. First_name$

Declaring a parameter to a subroutine or a function as string

= - Equal Sign

The equal sign appears in a number of places in Excel.

Formulas begin with =

Can be used to determine equality, i.e. if 2 values are the same, ex. if a = b then

Can be used for assignment, ex. a = 1

Can be used for parameters but prefixed with a colon :, Sort:=xlAscending

used in a for statement, ex. for i = 1 to 20

# - Hash or Number Sign

The Hash or Number sign can be used in:

Define a number as a double??

Excel uses to show the number is too large to fit in column width, ex.

# is used to denote a number is Double

print #1, Line Input #1, ... used for file number in sequential input

Number formatting - #,

0.00

Date constant #3/21/2021#

^ - Hat or Caret

3^2 raise the number 3 to the power of two aka squares the value

Ctrl key in a sendkeys or Onkey statement

+ - Plus Sign

Addition

String Concatenation

Shift key in an OnKey or SendKey statment

* - Asterisk or Star

Multiplication sign

Wildcard in a Like statement

/ - Forward Slash

Division sign

Date separator

. - Period

decimal point, ex. 23.7

Cents, ex. $15.22

Object.method separator

Object.property separator

.Property or .Method - within a "With - End With" block

, - Comma

formatting for thousands

Array separator, ex. a(3,2)

Parameter separator, ex. call GetDate(10, 12, 2020)

; - Semicolon

Ctrl+; generates the date

print #1,s; ' don't add linefeed

: - Colon

Used as a range indicator, ex. A1:C4 - the range of cells from A1 to C4

Used with := equal sign to set a parameter

Ctrl+: - sets the time

used to end a "GoTo label" label_name:

< - Less Than Sign

Used to test if a < b (a is less than b)

<> - Not Equal

<= - Less than or equal to

> - Greater than sign

Used to test if a > b (a is greater than b)

<> - not equal

>= - Greater than or equal to

( ) - Parenthesis

used to set precedence of calculations. Anything in the inner most parenthesis are calculated first

Used for Array reference, ex. a(7)

used for defining Sub or Functions, ex. Sub Main() or Function Sqrt(x)

used for Collections, ex. Sheets(3).select or Sheets("Table of contents").select

- - Dash

Minus sign, ex. -5

Subtraction, ex. 7 - 2

Date separator, ex. 2020-10-15

_ - Underscore

VBA continuation character

Separator for variables

[] - Brackets

Used in formulas to specify a file name [c:\temp.xls]sheet2!A2

Used in like statement like [a-z]

in VBA used to get results of an Excel Formula, ex. a = [=SUBTOTAL(3,B:B)]

in VBA from macro recorder Activecell.formulaR1C1 = "=If(RC[-32]=0,0,RC[-10]/RC[-32]

' - Single Quote

VBA Comment character

Formula the value following is text

Sheet names that have a space in them, ex. 'This is a sheet'!A2

in SQL should be converted to even number of single quotes

" - Double Quote

Used to delineate a string, ex. "this is a string"

Use "" two double quotes if string should include the quote, ex. "This is a ""String"""

\ - Back Slash

VBA integer division, ex. 10\3 = 3 where as 10/3 = 3.333333333

File seperator c:\temp\fiile.txt

network drive \\net\temp\file.txt

| - Vertical Bar aka Pipe Character

? - Question Mark

Used as like for files ex. dir("*.xls?")

@ - At Sign

Currency: @

% - Percent Sign

Percent value

format character to format number as a percentage

Like wildcard for SQL statements

Integer: %

& - Ampersand

String Concatenation

Long variable declaration: &

! - Exclamation Point
Single: !
Used in math as factorial sign but not in VBA
Used to separate sheet name from cell in a formula, ex. Sheet2!A2

&tilda; - Tilda

Not used

` - Left Single Quote

Use with Ctrl key to toggle spreadsheet from hiding formula in a cell to showing them

{ } - Curly Braces

arrays
array formulas
Sendkeys to enclose a key like {Tab}