extract numerical value from text?

[Kieran]

Hello,

I have several workbooks with several worksheets, each of which has a
cell which contains the following text:

Duration=1000ms

The value '1000' varies, from 500 to 4000, depending on the worksheet.
I need to write a macro that uses that value. Is there a way that I
can extract that numerical value from the cell, to use in a formula
elsewhere? This occurs on 24 sheets in 10 workbooks, so I'd rather not
have to type the number into a cell 240 times.

Thanks!
-Kieran

 

[Frank Kabel]

Hi
see your other post

 

[ged]

You could use the find & replace commands:

Selection.Replace What:="duration=", Replacement:="", LookAt:=xlPart
_
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False
_
ReplaceFormat:=False
Selection.Replace What:="ms", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False
_
ReplaceFormat:=Fals

 

[Ron Rosenfeld]

Hello,

I have several workbooks with several worksheets, each of which has a
cell which contains the following text:

Duration=1000ms

The value '1000' varies, from 500 to 4000, depending on the worksheet.
I need to write a macro that uses that value. Is there a way that I
can extract that numerical value from the cell, to use in a formula
elsewhere? This occurs on 24 sheets in 10 workbooks, so I'd rather not
have to type the number into a cell 240 times.

Thanks!
-Kieran

Assuming the value always starts after the "=", then:

=======================
Function foo(c As Range) As Double
Const eq As String = "="

foo = Val(Mid(c.Text, InStr(1, c.Text, eq) + 1, 255))

End Function
=====================
--ron