Replace or Substitute for COMBIN function
 

I have lots of lines in this format to convert into COMBIN function

(39c3 - 37c3)

In the above line I want to convert it to ((COMBIN(39,3)-(COMBIN(37,3))

I have so many lines in the above format. All I want to change the c to
COMBIN with values intact as I have give the example.

Some of the lines are in this format.
(45c6 / (6c5 x (39c1 - 37c1)))

The above line should become
((COMBIN(45,6))/((COMBIN(6,5)*((COMBIN(39,1)-((COMBIN(37,1))))))))

Thanks in advance.

Replace or Substitute for COMBIN function
 

One way:

I assumed you wanted the cells to become a formula. If not, delete the

"=" &

from the

sTemp = "=" & .Text

line. (I also didn't include a lot of your parens - but I couldn't
figure out your pattern):

Public Sub COMBFormula()
Const sCOMB As String = "COMBIN($$)"
Dim rCell As Range
Dim rTextCells As Range
Dim nPos As Long
Dim nStart As Long
Dim nChars As Long
Dim sTemp As String
Dim sTemp2 As String
On Error Resume Next
Set rTextCells = ActiveSheet.Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
On Error GoTo 0
If Not rTextCells Is Nothing Then
For Each rCell In rTextCells
With rCell
If .Text Like "*#c#*" Then
sTemp = "=" & .Text
nPos = InStr(sTemp, "c")
Do
sTemp2 = Replace(Replace( _
sTemp, ")", " "), "(", " ")
nStart = InStrRev(Left(sTemp2, nPos), " ") + 1
nChars = InStr(nPos, sTemp2, " ") - nStart
sTemp = Left(sTemp, nStart - 1) & _
Replace(sCOMB, "$$", _
Replace(Mid(sTemp, nStart, nChars), _
"c", ",")) & _
Mid(sTemp, nStart + nChars)
nPos = InStr(sTemp, "c")
Loop Until nPos = 0
End If
.Formula = Replace(sTemp, "x", "*")
End With
Next rCell
End If
End Sub


In article ,
"Jaja" wrote:

I have lots of lines in this format to convert into COMBIN function

(39c3 - 37c3)

In the above line I want to convert it to ((COMBIN(39,3)-(COMBIN(37,3))

I have so many lines in the above format. All I want to change the c to
COMBIN with values intact as I have give the example.

Some of the lines are in this format.
(45c6 / (6c5 x (39c1 - 37c1)))

The above line should become
((COMBIN(45,6))/((COMBIN(6,5)*((COMBIN(39,1)-((COMBIN(37,1))))))))

Thanks in advance.

Replace or Substitute for COMBIN function
 

Is this function for Excel? If yes then where do these lines go to? Sorry I
have never worked that way. Looks like its some form of programming like Java
or C language.

Replace or Substitute for COMBIN function
 

It's the VBA language, which is the language that Office macros are
written in.

See

http://www.mvps.org/dmcritchie/excel/getstarted.htm


In article ,
"Jaja" wrote:

Is this function for Excel? If yes then where do these lines go to? Sorry I
have never worked that way. Looks like its some form of programming like Java
or C language.

Replace or Substitute for COMBIN function
 

Sorry about the parenthesis disfigure. I have worked it out. Also I have
simplified it for each format as written after OR.

The original format
(45c6 / (6c5 x (39c1 - 37c1)))

OR
45c6
( _________________________ )
(6c5 X (39c1 - 37c1))


The above format should become like this
((COMBIN(45,6))/((COMBIN(6,5))*((COMBIN(39,1))-(COMBIN(37,1)))))

OR

(COMBIN(45,6))
( _______________________________________________ )
((COMBIN(6,5)) X ((COMBIN(39,1)) - (COMBIN(37,1))))


Easiest way to convert is just enclose it in bracket. For example

xcy should become (COMBIN(x,y))

x and y is enclosed in a bracket with COMBIN outside enclosed again with
outer bracket.

The rest of the bracket in the original format will stay intact.

Replace or Substitute for COMBIN function
 

xcy should become (COMBIN(x,y))

Just an additional idea. If you would like to try Regular Expressions, then
set a vba library reference to:
'// Microsoft VBScript Regular Expressions 5.5

This returns the following:
(45c6 / (6c5 x (39c1 - 37c1)))
(Combin(45,6) / (Combin(6,5) * (Combin(39,1) - Combin(37,1))))
678755

Sub Demo()
'// Your string variable:
Dim s As String
s = "(45c6 / (6c5 x (39c1 - 37c1)))"
Debug.Print s

'// Our Program:
Dim re As New RegExp
Const fx As String = "Combin($1,$2)"

With re
.Global = True
.IgnoreCase = True
.MultiLine = True
.Pattern = "(\d+)c(\d+)"

s = Replace(s, "x", "*")
s = .Replace(s, fx)
End With
Debug.Print s
Debug.Print Evaluate(s)
End Sub

HTH. :)
--
Dana DeLouis
Win XP & Office 2003


"Jaja" wrote in message
...
Sorry about the parenthesis disfigure. I have worked it out. Also I have
simplified it for each format as written after OR.

The original format
(45c6 / (6c5 x (39c1 - 37c1)))

OR
45c6
( _________________________ )
(6c5 X (39c1 - 37c1))


The above format should become like this
((COMBIN(45,6))/((COMBIN(6,5))*((COMBIN(39,1))-(COMBIN(37,1)))))

OR

(COMBIN(45,6))
( _______________________________________________ )
((COMBIN(6,5)) X ((COMBIN(39,1)) - (COMBIN(37,1))))


Easiest way to convert is just enclose it in bracket. For example

xcy should become (COMBIN(x,y))

x and y is enclosed in a bracket with COMBIN outside enclosed again with
outer bracket.

The rest of the bracket in the original format will stay intact.

Replace or Substitute for COMBIN function
 

I have lots of lines in this format to convert into COMBIN function

If you wanted to call this function in a loop, one wouldn't want to
initialize Re with each call.
Perhaps make Re a public variable as in this example. You may want to
remove the Evaluate line as well.

Option Explicit
Public Re As RegExp

Function ToCombin(s As String, Optional Eval As Boolean = False)
If Re Is Nothing Then Set Re = New RegExp
Re.Global = True
Re.Pattern = "(\d+)c(\d+)"
ToCombin = Replace(s, Chr(120), Chr(42)) 'x - *
ToCombin = Re.Replace(ToCombin, "Combin($1,$2)")
If Eval Then ToCombin = Evaluate(ToCombin)
End Function

Sub TestIt()
Const s As String = "(45c6 / (6c5 x (39c1 - 37c1)))"
'What you asked...
Debug.Print ToCombin(s)
'If you want to evaluate the equation
Debug.Print ToCombin(s, True) 'True to return number
End Sub

Returns:
(Combin(45,6) / (Combin(6,5) * (Combin(39,1) - Combin(37,1))))
678755

--
Dana DeLouis
Win XP & Office 2003


"Dana DeLouis" wrote in message
...
xcy should become (COMBIN(x,y))

Just an additional idea. If you would like to try Regular Expressions,
then set a vba library reference to:
'// Microsoft VBScript Regular Expressions 5.5

This returns the following:
(45c6 / (6c5 x (39c1 - 37c1)))
(Combin(45,6) / (Combin(6,5) * (Combin(39,1) - Combin(37,1))))
678755

Sub Demo()
'// Your string variable:
Dim s As String
s = "(45c6 / (6c5 x (39c1 - 37c1)))"
Debug.Print s

'// Our Program:
Dim re As New RegExp
Const fx As String = "Combin($1,$2)"

With re
.Global = True
.IgnoreCase = True
.MultiLine = True
.Pattern = "(\d+)c(\d+)"

s = Replace(s, "x", "*")
s = .Replace(s, fx)
End With
Debug.Print s
Debug.Print Evaluate(s)
End Sub

HTH. :)
--
Dana DeLouis
Win XP & Office 2003


"Jaja" wrote in message
...
Sorry about the parenthesis disfigure. I have worked it out. Also I have
simplified it for each format as written after OR.

The original format
(45c6 / (6c5 x (39c1 - 37c1)))

OR
45c6
( _________________________ )
(6c5 X (39c1 - 37c1))


The above format should become like this
((COMBIN(45,6))/((COMBIN(6,5))*((COMBIN(39,1))-(COMBIN(37,1)))))

OR

(COMBIN(45,6))
( _______________________________________________ )
((COMBIN(6,5)) X ((COMBIN(39,1)) - (COMBIN(37,1))))


Easiest way to convert is just enclose it in bracket. For example

xcy should become (COMBIN(x,y))

x and y is enclosed in a bracket with COMBIN outside enclosed again with
outer bracket.

The rest of the bracket in the original format will stay intact.