Separating Cell contents in VBA Strings

[SBF12345]

Greetings,

I would like to separate values held in a cell. The cell content is produced from a web query and is of the format below:

"11.00+1.00 (+10.00%)"

I would like to use the leftmost '.' (period) as a reference point if necessary. I would like to set as a variable value any numbers to the left of the left most period, the period, and the two digits to the right of the left most period. Essentiallly I would like to take the stock value from the entire cell contents. Any ideas on how best to do this?

 

[JoeMo]

Excel Workbook
	A	B
1	11.00+1.00 (+10.00%)	11.00
2	9.03+1.00 (+10.00%)	9.03
Sheet1

 

[SBF12345]

Thanks for the reply!

The VBA code that seems to be working is:

Range("A1").value = "=MID(A2,1,SEARCH(""."",A2)+2)"

 

[JoeMo]

Thanks for the reply!

The VBA code that seems to be working is:

Range("A1").value = "=MID(A2,1,SEARCH(""."",A2)+2)"

You are welcome.

If you want a numeric extract, in VBA (your extract is a string not a number), maybe:

With Range("A1")
     .Formula= "=MID(A2,1,SEARCH(""."",A2)+2)+0"
     .Value = .Value
End With

Assuming your string is in A2 and you want the numeric extract in A1.

 

[Rick Rothstein]

YIf you want a numeric extract, in VBA (your extract is a string not a number), maybe:

With Range("A1")
     .Formula= "=MID(A2,1,SEARCH(""."",A2)+2)+0"
     .Value = .Value
End With

Assuming your string is in A2 and you want the numeric extract in A1

You could also use this...

Range("A1") = Val(Range("A2"))