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.

<pre>
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</pre>

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.