extract numerical value from text?

  • Thread starter Thread starter Kieran
  • Start date Start date
K

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
 
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
 
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
Click to expand...

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