Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have one field that contains the color and size of a clothing item, eg. BLK06 or CCBLK06. What I want to do is to break it up into two fields: Color and Size. I know by using the Right function I can extract the size, but here is the thing: How on earth can I extract the first part of the field (color) without knowing how long the characters are (some can have one color: BLK or two combined like: CCBLK). I thought of telling Access to start from the right side of the field and ignore the first two characters and display the rest, etc. I have tried various things and am stubbed. It would really help if the BLK06 for example, had a space in between. Can anyone help, I would be forever grateful. P.S. I am also trying to do the same thing in Excel.

    Cheers!
    Tina

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='tinaw' post='769942' date='10-Apr-2009 16:58']I have one field that contains the color and size of a clothing item, eg. BLK06 or CCBLK06. What I want to do is to break it up into two fields: Color and Size. I know by using the Right function I can extract the size, but here is the thing: How on earth can I extract the first part of the field (color) without knowing how long the characters are (some can have one color: BLK or two combined like: CCBLK). I thought of telling Access to start from the right side of the field and ignore the first two characters and display the rest, etc. I have tried various things and am stubbed. It would really help if the BLK06 for example, had a space in between. Can anyone help, I would be forever grateful. P.S. I am also trying to do the same thing in Excel.

    Cheers!
    Tina[/quote]
    Does the size always occupy the last 2 chars?
    If so then try the Mid function:
    Mid(Field,1 ,Len(Field)-2)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    Slightly simpler than Pat's suggestion:

    Left([NameOfField], Len([NameOfField])-2)

    In Excel, you'd use a formula such as

    =LEFT(A3,LEN(A3)-2)

    where A3 is the cell containing the code.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    [quote name='tinaw' post='769942' date='10-Apr-2009 02:58']I have one field that contains the color and size of a clothing item, eg. BLK06 or CCBLK06. What I want to do is to break it up into two fields: Color and Size. I know by using the Right function I can extract the size, but here is the thing: How on earth can I extract the first part of the field (color) without knowing how long the characters are (some can have one color: BLK or two combined like: CCBLK). I thought of telling Access to start from the right side of the field and ignore the first two characters and display the rest, etc. I have tried various things and am stubbed. It would really help if the BLK06 for example, had a space in between. Can anyone help, I would be forever grateful. P.S. I am also trying to do the same thing in Excel.

    Cheers!
    Tina[/quote]
    I'm assuming there are some fixed aspects of this field: the last 2 characters are the #, and the 3 characters immediately before the last 2 characters are the color. Therefore, I'd use:

    Right( Left(myfield, len(myfield)-2), 3)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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