PC Review


Reply
Thread Tools Rate Thread

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.

 
Reply With Quote
 
 
 
 
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.
>



 
Reply With Quote
 
 
 
 
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.


 
Reply With Quote
 
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

 
Reply With Quote
 
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.
>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count until condition is met and then retain final count Fats Microsoft Excel Misc 2 24th Dec 2009 02:21 AM
Counting Zeros in a row until encounter first non-zero value Bubba Microsoft Excel Worksheet Functions 9 13th Oct 2009 04:42 AM
Extract everything to the right in cell when a number is encounter Matt Microsoft Excel Worksheet Functions 4 9th Jan 2008 04:25 PM
Want to copy a cell down until it comes to the next nonblank cell, and then take this next nonblank cell until it comes to the next nonblank cell, and so on... GretOgrady Microsoft Excel Discussion 2 5th Jan 2005 09:29 PM
Keyboard will not type until Shift key is held down until it beeps David Windows XP General 0 1st Dec 2003 06:35 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 08:21 AM.