Delimit using a formula 2

[laandmc]

I am using the functions

=MID(A1,FIND("(",A1)+1,FIND("/",A1)-FIND("(",A1)-1) and
=MID(A1,FIND("/",A1)+1,FIND(")",A1)-FIND("/",A1)-1)

to get a couple of numbers out of a cell which use the delimiters (, /, and )
e.g. text (2/3) more text

I am using this to pull out odds from a betting site, however if the odds
are displayed as just a whole number e.g. (2) this is not working

Is there a way to put an if function in or something to say "if the cell
contains an "/" character do the original formula, if not do a slightly
altered formula only containing "(" and ")".

Please see my post "Delimit using a formula" for further details, any help
would be greatly appreciated.

Thanks

 

[Fred Smith]

This way:
=if(isnumber(find("/",a1)),"has slash","no slash")

Regards,
Fred

 

[Queso hotmail com>]

Sure. Assuming you want the singular answer in the first formula and the
second formula to not even run:
=IF(FIND("/",A1),MID(A1,FIND("(",A1)+1,FIND("/",A1)-FIND("(",A1)-1),MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1))
and
=IF(FIND("/",A1),MID(A1,FIND("/",A1)+1,FIND(")",A1)-FIND("/",A1)-1),"")

 

[Ron Rosenfeld]

I am using the functions

=MID(A1,FIND("(",A1)+1,FIND("/",A1)-FIND("(",A1)-1) and
=MID(A1,FIND("/",A1)+1,FIND(")",A1)-FIND("/",A1)-1)

to get a couple of numbers out of a cell which use the delimiters (, /, and )
e.g. text (2/3) more text

I am using this to pull out odds from a betting site, however if the odds
are displayed as just a whole number e.g. (2) this is not working

Is there a way to put an if function in or something to say "if the cell
contains an "/" character do the original formula, if not do a slightly
altered formula only containing "(" and ")".

Please see my post "Delimit using a formula" for further details, any help
would be greatly appreciated.

Thanks

Looking at both your previous posts and this one, this problem is easily solved
with a User Defined Function using regular expressions.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=GetOdds(A1,[Index])

in some cell. Index indicates whether you want the number before the slash
(1) or after (2). If there is no slash, and Index = 2, then the function will
return a 1.

As written, this depends on the numbers that you want being between
parentheses; that this set of parentheses is the first in the string; and that
the numbers are whole numbers.

=============================================
Option Explicit
Function GetOdds(s As String, Optional Index As Long = 1)
Dim re As Object, mc As Object
Const sPat As String = "^[^(]+\((\d+)/?(\d*(?=\)))"
If Not (Index = 1 Or Index = 2) Then
GetOdds = CVErr(xlErrNum)
Exit Function
End If
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat
If re.test(s) = True Then
Set mc = re.Execute(s)
GetOdds = mc(0).submatches(Index - 1)
If GetOdds = "" Then GetOdds = 1
GetOdds = CLng(GetOdds)
End If
End Function
================================================
--ron

 

[gjlinker]

I have built some text functions into my Excel functions library litLIB that may be handy: SplitText, TextBetween.

Gerrit-Jan Linke
Author of LITLIB