# Thread: What does CODE do?

1. Loungers - I came across a spreadsheet that uses the formal below to calculate the numbers under the Score heading (sorry not sure how to insert a table) - I'm interested in understanding the value of using the CODE function in this situation as it seems to be overly complex for something that I believe is a fairly simple formula -

Weight Achieved Score
5 0 0
5 4 20
5 4 20
5 4 20
5 4 20
5 4 20
5 * N/A
120 100

=SUM(C47:C53)*4-IF(CODE(D47)=42,(C47*4),0)-IF(CODE(D48)=42,(C48*4),0)-IF(CODE(D49)=42,(C49*4),0)-IF(CODE(D50)=42,(C50*4),0)-IF(CODE(D51)=42,(C51*4),0)-IF(CODE(D52)=42,(C52*4),0)-IF(CODE(D53)=42,(C53*4),0)[list]

Any thoughts?

2. [quote name='verada' post='785871' date='23-Jul-2009 08:44']Loungers - I came across a spreadsheet that uses the formal below to calculate the numbers under the Score heading (sorry not sure how to insert a table) - I'm interested in understanding the value of using the CODE function in this situation as it seems to be overly complex for something that I believe is a fairly simple formula -

Weight Achieved Score
5 0 0
5 4 20
5 4 20
5 4 20
5 4 20
5 4 20
5 * N/A
120 100

=SUM(C47:C53)*4-IF(CODE(D47)=42,(C47*4),0)-IF(CODE(D48)=42,(C48*4),0)-IF(CODE(D49)=42,(C49*4),0)-IF(CODE(D50)=42,(C50*4),0)-IF(CODE(D51)=42,(C51*4),0)-IF(CODE(D52)=42,(C52*4),0)-IF(CODE(D53)=42,(C53*4),0)[list]

Any thoughts?[/quote]

=CODE(text) returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by computer. ANSI is character set used by Windows. This can be viewed in formula help.

[attachment=84814:untitled.JPG]

3. Further to Prasad's answer, 42 is the ASCII/ANSI code of the asterisk character *, so the condition

CODE(D47)=42

is equivalent to

the value of D47 starts with an *

How do I find out what the ASCII/ANSI code are and any thoughts on a simpler alternative?

5. See ANSI character set and equivalent Unicode and HTML characters.

If column D only contains a number or an asterisk *, the formula can be simplified to

=SUMIF(D4753,"<>~*",C47:C53)*4

6. Thanks Hans - that looks much easier.

As a matter of interest, what does ~ do?

7. The asterisk * acts as a wildcard in many situations in Excel, meaning "any number of characters". The ~ before it tells Excel to treat * as a literal character.

8. Thanks for all your help - AGAIN!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•