# Thread: Code confusion? (Excel 2002/2003)

1. ## Code confusion? (Excel 2002/2003)

Hi

I have inherited these codes, Ican see what is happening, but don't understand how.

I would appreciate and expanation of how the code is doing what it is doing and is there a shorter resolution.

2. ## Re: Code confusion? (Excel 2002/2003)

There is no (VBA) code in the workbook you attached, only formulas.

The formula in H2 returns the part of E2 to the left of the first space.
FIND(" ",E2,1) returns the position of the first space in E2, or an error if there is no space.
ISERROR(FIND(" ",E2,1)) checks if the FIND function returns an error, i.e. whether no space was found.
IF(ISERROR(FIND(" ",E2,1)),LEN(E2),FIND(" ",E2,1)-1) returns the length of E2 if no space was found, and the position of the space minus 1 otherwise.
LEFT(E2,IF(ISERROR(FIND(" ",E2,1)),LEN(E2),FIND(" ",E2,1)-1)) either returns the entire value of E2 if there is no space, otherwise the part before the first space.
The formula can be written slightly differently:

=IF(ISERROR(FIND(" ",E2)),E2,LEFT(E2,(FIND(" ",E2)-1)))

The formula in I2 returns the first two characters from H2 if they are both letters, or the first character if it is a letter and the second one is a digit (this is not exactly what it does, but never mind).
MID(H2,2,1) is the second character of H2.
CODE(MID(H2,2,1)) is the ASCII code of this character.
CODE(MID(H2,2,1))>64 is TRUE if the character is "A" or higher, FALSE otherwise, e.g. if it is a digit (the ASCII codes of the digits are 48 ... 57).
1+CODE(MID(H2,2,1))>64 is 2 if the character is "A" or higher, 1 otherwise, since TRUE = 1 and FALSE = 0 in Excel formulas.
LEFT(H2,1+(CODE(MID(H2,2,1))>64)) returns either the first 2 characters or the first character.
An alternative for this formula is

=LEFT(H2,1+(MID(H2,2,1)>="A"))

Hans

3. ## Re: Code confusion? (Excel 2002/2003)

If you're responding, shouldn't it be Reregads? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

4. ## Re: Code confusion? (Excel 2002/2003)

Hi Hans

I sorry I set you such a task, but thanks very much for the explanation and the shortened codes.