Find
 

Hi Group,
I am trying to find the position in a string variable to extract some
numbers. The string is (14)44, which is in a variable called WeekNo. I am
trying to extract the number with in the ( ), which can be one or two digits
long. I have tried using mm=application.worksheetfunction.find("(",WeekNo,2 )
to get the position, but this failed. I also tried
mm=application.worksheetfunction.find('(',WeekNo,2 ) and failed. Finding the
position is only an interim step to extracting the number and there may be an
easier way.

Thanks,
David

Find
 

Have a look in vba help for INSTR

--
Don Guillett
SalesAid Software

"David" wrote in message
...
Hi Group,
I am trying to find the position in a string variable to extract some
numbers. The string is (14)44, which is in a variable called WeekNo. I am
trying to extract the number with in the ( ), which can be one or two
digits
long. I have tried using
mm=application.worksheetfunction.find("(",WeekNo,2 )
to get the position, but this failed. I also tried
mm=application.worksheetfunction.find('(',WeekNo,2 ) and failed. Finding
the
position is only an interim step to extracting the number and there may be
an
easier way.

Thanks,
David

Find
 

Here it is, including conversion from string to number, in a one-step formula
(where KStr is the value in the cell):
=Val(Mid(KStr,InStr(KStr,"(")+1,InStr(KStr,")")-InStr(KStr,"(")-1))
Should work as long as you have the proper format; i.e. xxx(nn)yyy with only
numeric characters between the ().

"Don Guillett" wrote:

Have a look in vba help for INSTR

--
Don Guillett
SalesAid Software

"David" wrote in message
...
Hi Group,
I am trying to find the position in a string variable to extract some
numbers. The string is (14)44, which is in a variable called WeekNo. I am
trying to extract the number with in the ( ), which can be one or two
digits
long. I have tried using
mm=application.worksheetfunction.find("(",WeekNo,2 )
to get the position, but this failed. I also tried
mm=application.worksheetfunction.find('(',WeekNo,2 ) and failed. Finding
the
position is only an interim step to extracting the number and there may be
an
easier way.

Thanks,
David

Find
 

If the string always starts with ( then this should extract the number.

Sub useinstr()
mr = Range("weekno")
x = Mid(mr, 2, InStr(mr, ")") - 2)
MsgBox x
End Sub

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Have a look in vba help for INSTR

--
Don Guillett
SalesAid Software

"David" wrote in message
...
Hi Group,
I am trying to find the position in a string variable to extract some
numbers. The string is (14)44, which is in a variable called WeekNo. I
am
trying to extract the number with in the ( ), which can be one or two
digits
long. I have tried using
mm=application.worksheetfunction.find("(",WeekNo,2 )
to get the position, but this failed. I also tried
mm=application.worksheetfunction.find('(',WeekNo,2 ) and failed. Finding
the
position is only an interim step to extracting the number and there may
be
an
easier way.

Thanks,
David