# Count number of characters until encounter "&"

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

1. ### belkingoldGuest

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

2. ### Bernie DeitrickGuest

=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

3. ### Die_Another_DayGuest

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
4. ### titusGuest

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 & in A2
Put this code in A3 =LEFT(A1,FIND(A2,A1)-1)

Titus

titus, Aug 30, 2006
5. ### Bernie DeitrickGuest

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