Count number of characters until encounter "&"

  • Thread starter Thread starter belkingold
  • Start date Start date
B

belkingold

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.
 
=LEFT(A1,FIND("&",A1)-1)

or

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

HTH,
Bernie
MS Excel MVP
 
belkingold said:
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.
Click to expand...

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

Titus
 
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
 
Back
Top