| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count number of characters until encounter "&"
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count number of characters until encounter "&"
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count number of characters until encounter "&"
=LEFT(A1,FIND("&",A1)-1)
or =VALUE(LEFT(A1,FIND("&",A1)-1)) HTH, Bernie MS Excel MVP "belkingold" wrote in message oups.com... 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count number of characters until encounter "&"
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 ... =LEFT(A1,FIND("&",A1)-1) or =VALUE(LEFT(A1,FIND("&",A1)-1)) HTH, Bernie MS Excel MVP "belkingold" wrote in message oups.com... 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count number of characters until encounter "&"
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count characters within a cell to the left of "/" | New Users to Excel | |||
| Limiting a Column to "X" Number of Characters | Excel Discussion (Misc queries) | |||
| Limiting a Column to "X" Number of Characters | Excel Discussion (Misc queries) | |||
| The number of characters in a "Screen Tip" | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Hybrid Mode
