Excel Functions

Excel CODE & CHAR Functions, VBA Asc & Chr Functions

User Rating:  / 6
PoorBest 
Details

 

Excel Text and String Functions:

Excel CODE & CHAR Functions, VBA Asc & Chr Functions, with examples.

 

Related Links:

1. Using VBA Chr and Asc functions to convert excel column number to corresponding column letter, and column letter to column number.

2. ASCII Code, Extended ASCII characters (8-bit system) and ANSI Code.

3. Excel Text and String Functions: TRIM & CLEAN.

4. Excel Text and String Functions: LEFT, RIGHT, MID, LEN, FIND, SEARCH, REPLACE, SUBSTITUTE.

 


 

Excel CODE Function

 

Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer. Code returns the Macintosh character set for Macintosh computers and if you are using a Windows Operating System, the ANSI character code will be returned.

 

Syntax:  CODE(text)

 

Text   is the text for which you want the code of the first character.

 

Returns the code for the first character only, in the text string.

 

If the value does not equate to text, Code will return the #VALUE! error value.

 

 

In a Windows computer, Code function will return values as follows:

 

=CODE("A") returns 65.

=CODE("B") returns 66.

=CODE("a") returns 97.

=CODE("b") returns 98.

=CODE("0") returns 48.

=CODE("1") returns 49.

=CODE("-1") returns 45, which is the code for "-" (hyphen) .

=CODE(" ") , note the space between inverted commas, returns 32.

=CODE(""), no space between inverted commas, returns the #VALUE! error value.

=CODE("America") returns 65, which is the code for "A", the first character in the text string.

=CODE(A1) returns 66, using cell reference. If cell A1 contains the text "Bond", CODE(A1) returns 66, which is the code for "B", the first character in the text string.

=CODE("?") returns 63.

=CODE("™") returns 153.

 

 

Note that the Excel CODE function is the inverse of the Excel CHAR function. The formula =CODE(CHAR(65)) returns 65, because =CHAR(65) returns the character "A" and =CODE("A") returns 65.

 

The equivalent of excel CHAR function in vba is Chr(). Similarly, CODE function is the inverse of the Chr() vba function. Chr() assigns a character from a number value. If you are using a Windows Operating System, the character corresponding to the specified ANSI code will be returned. The CODE functions does the reverse by returning the numeric Chr() value of a character. For example, vba code of "MsgBox Chr(65)" returns the character "A".

 

The equivalent of excel CODE function in vba is Asc(). The Excel CHAR function is the inverse of the Asc() vba function. The Asc function determines the ANSI value of the first letter of a string.

 


 

Excel CHAR Function

 

Returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters.

 

Syntax:  CHAR(number)

 

Number   is a number between 1 and 255 specifying which character you want. The character is from the character set used by your computer. This will be the Macintosh character set for Macintosh computers and if you are using a Windows Operating System, the ANSI character set.

 

If the number argument is not a numeric value or is not an integer between 1 and 255, Char will return the #VALUE! error value.

 

 

In a Windows computer, Char function will return values as follows:

 

=CHAR(65) returns the character "A".

=CHAR(37) returns the character "%".

=CHAR(260) returns the #VALUE! error value.

=CHAR(163) returns the character "£".

=CHAR(A1) returns the character "B", using cell reference. If cell A1 contains the number 66, CHAR(A1) returns the character "B".

 

 

The equivalent of excel CHAR function in vba is Chr(). Note that the Excel CHAR function is the inverse of the Excel CODE function. The formula =CHAR(CODE("A")) returns A, because =CODE("A") returns the number 65 and =CHAR(65) returns A.

 


 

Examples of using the Excel CODE & CHAR Functions

 

 

Changing case (uppercase / lowercase) of alphabets in a string

 

Formula  =CHAR(CODE(A1)+32) returns the lowercase letter "b", if cell A1 contains the uppercase letter "B". This is because in the ANSI character set, the lowercase alphabets appear after uppercase alphabets, in an alphabetic order,  with a difference of exactly 32 numbers. Similarly, =CHAR(CODE(A1)-32) returns the uppercase letter "B", if cell A1 contains the lowercase letter "b".

 

Convert first character of the first word in a text string, to lowercase, and first character of all subsequent words to uppercase. To convert the string "we like james bond" in cell A1 to "we Like James Bond" use the following formula:

 

=REPLACE(PROPER(A1),1,1,CHAR(CODE(LEFT(PROPER(A1)))+32))  [Formula]

 

Formula part   =PROPER(A1)   returns the text string "We Like James Bond".

 

Formula part   =CHAR(CODE(LEFT(PROPER(A1)))+32)   returns the lowercase text "w".

 

The formula replaces the first character of the text, by first capitalizing it and then converting it to lowercase using CHAR/CODE functions.

 

 

It may however be noted, that the excel UPPER (or LOWER) function can also be used as alternate to CHAR/CODE functions. In this case the Formula  =REPLACE(PROPER(A1),1,1,LOWER(LEFT(PROPER(A1)))) will also give the same result and return the string "we Like James Bond".

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------

 

Making the above formula more complex:

 

Convert first character of the second word in a text string to lowercase, and the first character of all other words to uppercase (wherein the first and second word are separated by a single space). To convert the string "we Like James bond" in cell A1 to "We like James Bond" use the following formula: 

 

=REPLACE(PROPER(A1),FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1),CHAR(CODE(MID

(PROPER(A1),FIND(" ",A1)+1,1))+32)&RIGHT(PROPER(A1),LEN(A1)-FIND(" ",A1)-1))  [Formula]

 

Formula part   =PROPER(A1)   returns text string "We Like James Bond".

 

Formula part   =FIND(" ",A1)+1   returns the position 4.

 

Formula part   =LEN(A1)-FIND(" ",A1)   returns the value 15.

 

Formula part   =CHAR(CODE(MID(PROPER(A1),FIND(" ",A1)+1,1))+32)   returns the text "l".

 

Formula part   =REPLACE(PROPER(A1),FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1), _

CHAR(CODE(MID(PROPER(A1),FIND(" ",A1)+1,1))+32))   returns the text "We l".

 

Formula part   =RIGHT(PROPER(A1),LEN(A1)-FIND(" ",A1)-1)   returns text "ike James Bond".

 

Finally, the formula joins the text returned in the last 2 steps "We l" & "ike James Bond".

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------  

 

For a text string with irregular spacing (ie. having lead spaces and multiple spaces between words), we have modified the above formula to include the TRIM function which removes all spaces from text except for single spaces between words, and converts first character of the second word in a text string to lowercase, and the first character of all other words to uppercase :

 

=REPLACE(PROPER(TRIM(A1)),FIND(" ",TRIM(A1))+1,LEN(TRIM(A1))-FIND

(" ",TRIM(A1)),CHAR(CODE(MID(PROPER(TRIM(A1)),FIND(" ",TRIM(A1))+1,1))

+32)&RIGHT(PROPER(TRIM(A1)),LEN(TRIM(A1))-FIND(" ",TRIM(A1))-1))

 

Note: In addition to the CHAR, CODE, LEFT, MID & RIGHT functions, we have also used the excel PROPER function. Syntax: PROPER(text).  It capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------

 

For many letters and controls, it might be much easier to format or otherwise manipulate data with their ANSI codes than using your keyboard.

 

 

If the address is currently split in different cells, and you wish to consolidate in a single cell in multiple lines, use the excel CHAR function as follows (refer Image 1):

 

=A1 & CHAR(10) & B1 & CHAR(10) & C1 & CHAR(10) & D1

 

The address is currently split in cells A1 to D1 and the above formula consolidates and formats into a single cell in multiple lines.

 

10 is the ANSI code (number) for line feed, and it provides the line breaks to format data.

 

Ensure that the cell text wrapping is enabled, by clicking on the "Wrap Text" option.

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------

 

Format a single line address into multiple lines, using the excel CHAR & SUBSTITUTE functions as follows (refer Image 2):

 

=SUBSTITUTE(A1,", ",CHAR(10))

 

The address is currently mentioned in cell A1 in a single line, and the above formula formats it (in cell A3) by removing commas and into multiple lines.

 

10 is the ANSI code (number) for line feed, and it provides the line breaks to format data.

 

Ensure that the cell text wrapping is enabled, by clicking on the "Wrap Text" option.

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------

 

NOTE: To format an address with line breaks (appearing in multiple lines in cell A1) to a single line format (cell A3), use the formula (refer Image 3): =SUBSTITUTE(A1,CHAR(10),", ") 

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------  

 

To insert special characters or symbols in a text string, use the excel CHAR function:

 

="Exchange Rates Graph of Pound (" & CHAR(163) & "), Euro (" & CHAR(128) & ") and Dollar (" & (CHAR(36) & ")")

 

The above formula inserts currency symbols, using the CHAR function.

 

Returns the text string: "Exchange Rates Graph of Pound (£), Euro(€) and Dollar ($)"

 


 

Examples of using VBA Chr & Asc functions:

 

 

MsgBox Chr(65), returns the letter "A".

 

MsgBox Asc("A"), returns the number 65.

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------  

 

Sub replaceChar()
'using vba Chr() function to replace characters in a string


Dim str As String


str = Replace("ABC", Chr(65), Chr(66))
'replaces letter "A" with "B"


MsgBox str
'returns the text "BBC"


End Sub

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------  

 

Sub textFormat1()
'using vba chr() function to remove commas and provide line breaks.


Sheet1.Cells(3, 1) = Replace(Sheet1.Cells(1, 1), ", ", Chr(10))
'Chr(10) provides line feed/new line, and it replaces all commas in the text string. Refer Image 4.


End Sub

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------  

 

Sub textFormat2()
'inserting symbols using vba chr() function


Dim str As String


str = "Exchange Rates Graph of Pound (" & Chr(163) & "), Euro (" & Chr(128) & ") and Dollar (" & (Chr(36) & ")")
'evaluates to text string "Exchange Rates Graph of Pound (£), Euro (€) and Dollar ($)"


MsgBox str
'displays the text string "Exchange Rates Graph of Pound (£), Euro (€) and Dollar ($)"


End Sub

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------  

 

Sub deleteSpaces()
'remove spaces from a text string, in vba, using Substitute, Len, Mid & Chr functions


Dim str As String, n As Integer, a As Integer 

 

str = "Removing Spaces In Text String"

 

a = Len(str)

 

For n = 1 To a

 

If Mid(str, n, 1) = Chr(32) Then

'Chr(32) determines spaces in the string

str = Application.Substitute(str, Mid(str, n, 1), "")

End If

 

Next n


MsgBox str
'returns the text "RemovingSpacesInTextString"


End Sub

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------  

 

Sub removeNumbers()
'remove characters, say all numericals, from a text string, in vba, using Substitute, Len, Mid & Chr functions


Dim str As String, n As Integer, a As Integer, i As Integer

 

str = "Removing Numbers 12In4 Text6 9String5"

 

a = Len(str)

 

For n = 1 To a

 

For i = 48 To 57
'chr(48) to chr(57) represent numericals 0 to 9 in ANSI/ASCII character codes


If Mid(str, n, 1) = Chr(i) Then

str = Application.Substitute(str, Mid(str, n, 1), "")

End If


Next i


Next n


MsgBox str
'returns text "Removing Numbers In Text String"


End Sub


-----------------------------------------------------------------------------------------------------------------------------------------------------------


VBA Codes:

Using VBA Chr and Asc functions to convert excel column number to corresponding column letter, and column letter to column number.

 

 

   

Bottom Ad

   
   
© 2014 GlobaliConnect.com. All rights reserved.