Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    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.

    Please see attached

    Regads

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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"))

    Egads,
    Hans

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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>
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    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.

    I am in your debt.

    I thought Egads was dutch for Gadzooks!!. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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