# Count number of characters until encounter "&"

belkingold
Guest
Posts: n/a

 30th Aug 2006
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
Guest
Posts: n/a

 30th Aug 2006
=LEFT(A1,FIND("&",A1)-1)

or

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

HTH,
Bernie
MS Excel MVP

"belkingold" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
Guest
Posts: n/a

 30th Aug 2006
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.

titus
Guest
Posts: n/a

 30th Aug 2006

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

Bernie Deitrick
Guest
Posts: n/a

 30th Aug 2006
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:(E-Mail Removed)...
> =LEFT(A1,FIND("&",A1)-1)
>
> or
>
> =VALUE(LEFT(A1,FIND("&",A1)-1))
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "belkingold" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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.
>>

>
>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Fats Microsoft Excel Misc 2 24th Dec 2009 02:21 AM Bubba Microsoft Excel Worksheet Functions 9 13th Oct 2009 04:42 AM Matt Microsoft Excel Worksheet Functions 4 9th Jan 2008 04:25 PM GretOgrady Microsoft Excel Discussion 2 5th Jan 2005 09:29 PM David Windows XP General 0 1st Dec 2003 06:35 PM

Features