Count number of characters until encounter "&"

Discussion in 'Microsoft Excel Programming' started by belkingold, Aug 30, 2006.

  1. belkingold

    belkingold Guest

    I have a cell with the value "577256&S_MSGNUM=4777037808398&". I want
    to count the number of characters that appear before the first & symbol
    so I can do a left() on them. They are always integers, never letters.
     
    belkingold, Aug 30, 2006
    #1
    1. Advertisements

  2. =LEFT(A1,FIND("&",A1)-1)

    or

    =VALUE(LEFT(A1,FIND("&",A1)-1))

    HTH,
    Bernie
    MS Excel MVP


    "belkingold" <> wrote in message
    news:...
    >I have a cell with the value "577256&S_MSGNUM=4777037808398&". I want
    > to count the number of characters that appear before the first & symbol
    > so I can do a left() on them. They are always integers, never letters.
    >
     
    Bernie Deitrick, Aug 30, 2006
    #2
    1. Advertisements

  3. CharactersBefore& = Instr(1,"577256&S_MSGNUM=4777037808398&", "&") - 1

    Charles

    belkingold wrote:
    > I have a cell with the value "577256&S_MSGNUM=4777037808398&". I want
    > to count the number of characters that appear before the first & symbol
    > so I can do a left() on them. They are always integers, never letters.
     
    Die_Another_Day, Aug 30, 2006
    #3
  4. belkingold

    titus Guest

    belkingold wrote:
    > I have a cell with the value "577256&S_MSGNUM=4777037808398&". I want
    > to count the number of characters that appear before the first & symbol
    > so I can do a left() on them. They are always integers, never letters.


    Put your value in A1
    Put & in A2
    Put this code in A3 =LEFT(A1,FIND(A2,A1)-1)

    Titus
     
    titus, Aug 30, 2006
    #4
  5. Yikes! VBA:

    Sub TryNow()
    Dim myStr As String
    Dim myVal As Double

    myStr = Left(Range("A1").Value, InStr(1, Range("A1").Value, "&") - 1)
    MsgBox myStr
    myVal = CDbl(myStr)
    MsgBox Format(myVal, "0.00")
    End Sub

    HTH,
    Bernie
    MS Excel MVP


    "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
    news:...
    > =LEFT(A1,FIND("&",A1)-1)
    >
    > or
    >
    > =VALUE(LEFT(A1,FIND("&",A1)-1))
    >
    > HTH,
    > Bernie
    > MS Excel MVP
    >
    >
    > "belkingold" <> wrote in message
    > news:...
    >>I have a cell with the value "577256&S_MSGNUM=4777037808398&". I want
    >> to count the number of characters that appear before the first & symbol
    >> so I can do a left() on them. They are always integers, never letters.
    >>

    >
    >
     
    Bernie Deitrick, Aug 30, 2006
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jim May
    Replies:
    2
    Views:
    211
    Don Guillett
    Dec 24, 2004
  2. Replies:
    1
    Views:
    1,295
    Doug Glancy
    Feb 8, 2007
  3. kiwis

    need some help, encounter run-time error 1004

    kiwis, Jun 8, 2007, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    306
    Susan
    Jun 8, 2007
  4. Sing

    Simple If statement encounter error

    Sing, Sep 23, 2007, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    157
    Dave Peterson
    Sep 23, 2007
  5. JenIT
    Replies:
    2
    Views:
    509
    JenIT
    Aug 24, 2010
Loading...

Share This Page