Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Separating Text (2002)

    Hi,

    I have field [available time] which is in text format with values that contain a number followed by a letter. The number can contain a decimal and/or more than one number. For example 1V, 2H, 1.5H etc. I need to break away the letter so that only the number remains. I tried using a formula from a post I made in 2005 but it gave me an error. I'm not sure what is wrong:

    Available Time 2: Left([available time],InStr([available time],"_")-1)

    Thanks!
    Leesha

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Separating Text (2002)

    The formula is looking for a _ in the string and it is not there.

    If there is always a single letter on the end , then

    Available time2 : Left([Available time],Len([Available time])-1)


    this just removes the last character.
    Regards
    John



  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Separating Text (2002)

    Thanks! That worked! In my need to understand the code, what does "Len" stand for?

    Leesha

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

    Re: Separating Text (2002)

    Len([Available time]) returns the length (number of characters) of the value of [Available time].

    Len([Available time])-1 subtracts 1 from the length.

    Left([Available time],Len([Available time])-1) returns all characters except the last one (the alphabetic character).

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Separating Text (2002)

    Another question. How would I break this apart so I only had the letter? It will always be at the end without a separator inbetween.

    Thanks!
    Leesha

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

    Re: Separating Text (2002)

    Right([Available time],1)

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Separating Text (2002)

    Awesome!!!

    Thanks,
    Alicia

Posting Permissions

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