Reading certain characters from a cell

[Derek_35]

I'm opening a file I have no control over and cell D24 reads as follows :

Cash Left: ###,###

I want to only read in the # into an integer, not a string. How can I do this?

Thanks...

[Mark O'Brien]

Not sure what you mean, but would sticking:

=INT(D24)

in a cell be any good to you?

[Derek_35]

Cell D24 = "Cash Left: 392,000"

dim money as integer

I want an integer variable (money) equal to 392,000.

I can read the the cells value as a string, but a) I do not want the "Cash Left:" part and b) I want 392,000 as an integer, not a string.

[Mark O'Brien]

Try this, changing the value of sMoney to whatever cell you want.

Public Sub main()

'Cell D24 = "Cash Left: 392,000"

  Dim sMoney As String

  Dim iMoney As Long



   sMoney = "Cash Left: 392,000"

   iMoney = LTrim(RightString(sMoney, ":"))

   

   MsgBox iMoney

End Sub

Private Function RightString(ByVal sText As String, ByVal sSeparator As String) As String

  RightString = Right(sText, Len(sText) - InStr(1, sText, sSeparator))

End Function

I'm assuming that you are always going to have colon in the sentence before the number.

HTH

[Jay Petrulis]

Hi,

Are you reading the cell into a variable, or do you want to change the cell on the worksheet?

If the latter, try:

Sub test()
Dim cell As Range

For Each cell In Selection
cell = WorksheetFunction.Substitute(cell, "Cash Left:", "") + 0
Next cell
End Sub

Note also that you cannot read your example into an Integer variable. Change it to Long or Double.

myVari = WorksheetFunction.Substitute(cell, "Cash Left:", "") + 0

If you have Excel 2000 or XP, I believe there is a Replace function that can be used instead of worksheetfunction.substitute

HTH,
Jay

EDIT: Scratch this answer. Mark's is nicer.

[ This Message was edited by: Jay Petrulis on 2002-04-22 13:26 ]

[Derek_35]

Thanks! Works like a charm....

[Mark O'Brien]

Oh for God's sakes Jay! You're just far too nice to me.

I liked your solution. I wouldn't even have considered that, because I didn't know it existed.