software

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.

One way:

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

quot;=quot; amp;

from the

sTemp = quot;=quot; amp; .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 = quot;COMBIN($$)quot;
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 quot;*#c#*quot; Then
sTemp = quot;=quot; amp; .Text
nPos = InStr(sTemp, quot;cquot;)
Do
sTemp2 = Replace(Replace( _
sTemp, quot;)quot;, quot; quot;), quot;(quot;, quot; quot;)
nStart = InStrRev(Left(sTemp2, nPos), quot; quot;) 1
nChars = InStr(nPos, sTemp2, quot; quot;) - nStart
sTemp = Left(sTemp, nStart - 1) amp; _
Replace(sCOMB, quot;$$quot;, _
Replace(Mid(sTemp, nStart, nChars), _
quot;cquot;, quot;,quot;)) amp; _
Mid(sTemp, nStart nChars)
nPos = InStr(sTemp, quot;cquot;)
Loop Until nPos = 0
End If
.Formula = Replace(sTemp, quot;xquot;, quot;*quot;)
End With
Next rCell
End If
End SubIn article gt;,
quot;Jajaquot; gt; wrote:

gt; I have lots of lines in this format to convert into COMBIN function
gt;
gt; (39c3 - 37c3)
gt;
gt; In the above line I want to convert it to ((COMBIN(39,3)-(COMBIN(37,3))
gt;
gt; I have so many lines in the above format. All I want to change the c to
gt; COMBIN with values intact as I have give the example.
gt;
gt; Some of the lines are in this format.
gt; (45c6 / (6c5 x (39c1 - 37c1)))
gt;
gt; The above line should become
gt; ((COMBIN(45,6))/((COMBIN(6,5)*((COMBIN(39,1)-((COMBIN(37,1))))))))
gt;
gt; Thanks in advance.

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.

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

See

www.mvps.org/dmcritchie/excel/getstarted.htmIn article gt;,
quot;Jajaquot; gt; wrote:

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

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.

gt; 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 = quot;(45c6 / (6c5 x (39c1 - 37c1)))quot;
Debug.Print s

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

With re
.Global = True
.IgnoreCase = True
.MultiLine = True
.Pattern = quot;(\d )c(\d )quot;

s = Replace(s, quot;xquot;, quot;*quot;)
s = .Replace(s, fx)
End With
Debug.Print s
Debug.Print Evaluate(s)
End Sub

HTH. :gt;)
--
Dana DeLouis
Win XP amp; Office 2003quot;Jajaquot; gt; wrote in message
...
gt; Sorry about the parenthesis disfigure. I have worked it out. Also I have
gt; simplified it for each format as written after OR.
gt;
gt; The original format
gt; (45c6 / (6c5 x (39c1 - 37c1)))
gt;
gt; OR
gt; 45c6
gt; ( _________________________ )
gt; (6c5 X (39c1 - 37c1))
gt;
gt;
gt; The above format should become like this
gt; ((COMBIN(45,6))/((COMBIN(6,5))*((COMBIN(39,1))-(COMBIN(37,1)))))
gt;
gt; OR
gt;
gt; (COMBIN(45,6))
gt; ( _______________________________________________ )
gt; ((COMBIN(6,5)) X ((COMBIN(39,1)) - (COMBIN(37,1))))
gt;
gt;
gt; Easiest way to convert is just enclose it in bracket. For example
gt;
gt; xcy should become (COMBIN(x,y))
gt;
gt; x and y is enclosed in a bracket with COMBIN outside enclosed again with
gt; outer bracket.
gt;
gt; The rest of the bracket in the original format will stay intact.
gt; 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 = quot;(\d )c(\d )quot;
ToCombin = Replace(s, Chr(120), Chr(42)) 'x -gt; *
ToCombin = Re.Replace(ToCombin, quot;Combin($1,$2)quot;)
If Eval Then ToCombin = Evaluate(ToCombin)
End Function

Sub TestIt()
Const s As String = quot;(45c6 / (6c5 x (39c1 - 37c1)))quot;
'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 amp; Office 2003quot;Dana DeLouisquot; gt; wrote in message
...
gt;gt; xcy should become (COMBIN(x,y))
gt;
gt; Just an additional idea. If you would like to try Regular Expressions,
gt; then set a vba library reference to:
gt; '// Microsoft VBScript Regular Expressions 5.5
gt;
gt; This returns the following:
gt; (45c6 / (6c5 x (39c1 - 37c1)))
gt; (Combin(45,6) / (Combin(6,5) * (Combin(39,1) - Combin(37,1))))
gt; 678755
gt;
gt; Sub Demo()
gt; '// Your string variable:
gt; Dim s As String
gt; s = quot;(45c6 / (6c5 x (39c1 - 37c1)))quot;
gt; Debug.Print s
gt;
gt; '// Our Program:
gt; Dim re As New RegExp
gt; Const fx As String = quot;Combin($1,$2)quot;
gt;
gt; With re
gt; .Global = True
gt; .IgnoreCase = True
gt; .MultiLine = True
gt; .Pattern = quot;(\d )c(\d )quot;
gt;
gt; s = Replace(s, quot;xquot;, quot;*quot;)
gt; s = .Replace(s, fx)
gt; End With
gt; Debug.Print s
gt; Debug.Print Evaluate(s)
gt; End Sub
gt;
gt; HTH. :gt;)
gt; --
gt; Dana DeLouis
gt; Win XP amp; Office 2003
gt;
gt;
gt; quot;Jajaquot; gt; wrote in message
gt; ...
gt;gt; Sorry about the parenthesis disfigure. I have worked it out. Also I have
gt;gt; simplified it for each format as written after OR.
gt;gt;
gt;gt; The original format
gt;gt; (45c6 / (6c5 x (39c1 - 37c1)))
gt;gt;
gt;gt; OR
gt;gt; 45c6
gt;gt; ( _________________________ )
gt;gt; (6c5 X (39c1 - 37c1))
gt;gt;
gt;gt;
gt;gt; The above format should become like this
gt;gt; ((COMBIN(45,6))/((COMBIN(6,5))*((COMBIN(39,1))-(COMBIN(37,1)))))
gt;gt;
gt;gt; OR
gt;gt;
gt;gt; (COMBIN(45,6))
gt;gt; ( _______________________________________________ )
gt;gt; ((COMBIN(6,5)) X ((COMBIN(39,1)) - (COMBIN(37,1))))
gt;gt;
gt;gt;
gt;gt; Easiest way to convert is just enclose it in bracket. For example
gt;gt;
gt;gt; xcy should become (COMBIN(x,y))
gt;gt;
gt;gt; x and y is enclosed in a bracket with COMBIN outside enclosed again with
gt;gt; outer bracket.
gt;gt;
gt;gt; The rest of the bracket in the original format will stay intact.
gt;
gt;