processing a string by Excel

**mark_neil2** · 06-08-2007, 08:20 PM

Hi,
I get equation in the form of a string from the output of a software that I want to modify by Excel. I am no expert in Excel and I am wondering if some expert in Excel at this forum can do this for me.
Pls see the attached Excel file. The string in cell A1 is an example string that I will be explaining how I want this string to be modified. Here are the attributes of the string.

a. The string will have ASCII characters in it.

b. The string is a mathematical equation. I will be pasting it into Mathcad once the Excel program modifies it.

c. The string has parameters and mathematical operators in it (+,-,*,/,^). Each parameter is seperated from another parameter by at least one space. There is no space in a parameter. So, each parameter starts with a space and ends with a space. The parameters of the example string in cell A1 are listed from cells A5 to A14 in the attached file.

d. If a parameter has paranthesis in it (), then the program will disregard any character in the parameter outside the paranthesis and take the characters inside the paranthesis. For example, it will modify the parameter in cell A5 from "F(u)_X1" to "u". If the parameter has no paranthesis in it, like the one in cell A14, then it will take it as is.

e. The format of the string is such that the multiplication sign may be omitted between parameters. If there is no mathematical operator between two adjacent parameters, it should be assumed that the math operator between them is multiplication. So, the Excel program should insert a multiplication sign between them.

f. Then the program will reassemple the string in the form of an equation by appropriately keeping the mathematical operators in the original expression.

The cells B5 to B14 show the modified parameters of the original parameters in the string in cell A1. The Excel program should put together the parameters in B5 to B14 as shown in cell A18.

I will appreciate if someone can write an Excel program for me that accomplishes this. Many thanks.

Mark Neil

**mudraker** · 06-09-2007, 07:26 AM

I think this will do what you want - It can only cope with one lot of multiple (( & )) brackets

It only looks at cell A1 & displays the result in a message box

Code can be modified to run down a column & place the result into another column

Sub ConvertTxtToFormula()
   Dim i4Cnt As Integer
   Dim sCellTxt As String
   Dim sParam As String
   Dim sChar As String
   Dim sFormula As String
   
   Dim iPos(4) As Integer
   
   sCellTxt = Range("a1").Value
   
   For i4Cnt = 1 To Len(sCellTxt) Step 1
      sChar = Mid(sCellTxt, i4Cnt, 1)
      Select Case sChar
      Case "("
         If i4Cnt = 1 Then
            iPos(0) = 1
            iPos(1) = i4Cnt
         Else
            iPos(1) = i4Cnt + 1
         End If
      Case ")"
         If Mid(sCellTxt, i4Cnt - 1, 2) = "))" Then
            iPos(1) = i4Cnt - 1
         End If
         iPos(0) = (i4Cnt) - iPos(1)
      Case "-", "+,", "/", "*", "^"
         iPos(1) = i4Cnt
         iPos(0) = 1
      End Select
      If iPos(0) > 0 Then
         sParam = Mid(sCellTxt, iPos(1), iPos(0))
         iPos(0) = 0
         Select Case Right(sFormula, 1)
         Case "-", "+,", "/", "*", "^", ""
            sFormula = sFormula & sParam
         Case Else
            Select Case sParam
            Case "-", "+,", "/", "*", "^"
               sFormula = sFormula & sParam
            Case Else
               sFormula = sFormula & "*" & sParam
            End Select
            If sParam = ")" Then
               sParam = Trim(Mid(sCellTxt, i4Cnt + 1))
               Select Case sParam
               Case "-", "+,", "/", "*", "^"
                  sFormula = sFormula & sParam
               Case Else
                  sFormula = sFormula & "*" & sParam
               End Select
               Exit For
            End If
         End Select
      End If
   Next i4Cnt
   MsgBox sFormula
End Sub

**mark_neil2** · 06-09-2007, 08:13 PM

Hi Mudraker,
Thanks a lot for your reply. I appreciate it.
I have noticed an error in the expression. There is a redundant * sign right before the closing paranthesis in the example in Book1.xls file. It should not there. Also, I have run the script on more examples and noticed that it doesn't do quite what it is supposed to do. One more thing is that I will copy the resultant expression and paste it in Mathcad. Can the program write the result in a cell instead of message box?
I have attached another Excel file (Book11.xls) to this post including more examples for string. Cell A1 is the same example as you worked on. Cells A2 and A3 are new examples to test the script for.
The strings have parantesis that grabs the whole expression as well as paranthesis in the string parameters. It requires extra effort in programming to diffrentiate between them. Therefore, I have the option to use some other type of parantesis in the parameters. For example, I can use square brackets "[]" or curly parantesis "{}" in the parameters. I think this may make the programming easier since the program keeps the paranthesis as is () and detects the square brackets (or or curly paranthesis) in the parameters and modify the expression accordingly. Cell A4 in the attached file has an expression that uses square brackets within parameters. The expression in A4 is the same expression as in A3 except the square brackets. Cells A7 to A9 has the modified expressions of the strings in A1 to A3, respectively.

Thanks in advance.

Mark Neil

**mark_neil2** · 06-13-2007, 08:03 PM

Norm,
Thanks for your help.
I have added three more examples of string in the file attached (Book111.xls). The first three are the same as in Book11.xls. But I changed them so that parameters have brackets instead of paranthesis.
Strings from A4 to A6 are new.
I have noticed that the parameters in the string A1 have extra characters after a closing bracket "]" while all the parameters in the strings in A2 and A3 end with a bracket. I have a hunch that if you add to the program the intelligence that it ignores rest of the parameter until the next space once it detects a closing bracket, it would work just fine.
Thanks.

Mark

**mudraker** · 06-17-2007, 04:24 AM

Mark

It's taken a while to get all the bugs out.

The answers you supplied for the last 2 equations, to me appears to be incorrect - You left a ] in both of them & the last one is missing the 1st )

The extra examples helped me to code the macro to deliver the results you are after

This macro loops through column A starting at row 1 to the last used row in column A, Places the result in column B of the same row

Please test as fully as possible.

Sub ExtractFormula()
   Dim bAdd As Boolean
   
   Dim iTxtLen As Integer
   Dim i4Len As Integer
   
   Dim l4Row As Long
   
   Dim sCval As String
   Dim sFormula As String
   Dim sParam As String
   Dim sOp As String
   
   For l4Row = 1 To Cells(Rows.Count, "a").End(xlUp).Row Step 1
      sCval = Cells(l4Row, "a")
      iTxtLen = Len(sCval)
      
      bAdd = True
      For i4Len = 1 To iTxtLen Step 1
         Select Case Mid(sCval, i4Len, 1)
         Case "(", ")"
            If Len(sOp) = 1 Then
               sFormula = sFormula & sOp & sParam
               sOp = vbNullString
            Else
               If Len(sParam) > 0 Then
                  Select Case sParam
                  Case "-", "+", "/", "*", "^"
                     sFormula = sFormula & sParam
                  Case Else
                     sFormula = sFormula & "*" & sParam
                  End Select
               End If
            End If
            sParam = vbNullString
            sFormula = sFormula & Mid(sCval, i4Len, 1)
            bAdd = True
         Case " "
            bAdd = True
            If Len(sParam) > 0 Then
               Select Case sParam
               Case "-", "+", "/", "*", "^"
                  sOp = sParam
               Case Else
                  If Len(sOp) = 1 Then
                     sFormula = sFormula & sOp & sParam
                     sOp = vbNullString
                  Else
                     If Right(sFormula, 1) = "(" Then
                        sFormula = sFormula & sParam
                     Else
                        Select Case Left(sParam, 1)
                        Case "-", "+", "/", "*", "^"
                           sFormula = sFormula & sParam
                        Case Else
                           sFormula = sFormula & "*" & sParam
                        End Select
                     End If
                  End If
               End Select
            End If
            sParam = vbNullString
         Case Else
            Select Case Mid(sCval, i4Len, 1)
            Case "["
               sParam = vbNullString
            Case "]"
               bAdd = False
            Case Else
               If bAdd = True Then
                  sParam = sParam & Mid(sCval, i4Len, 1)
               End If
            End Select
         End Select
      Next i4Len
      If Len(sParam) > 0 Then
         If Len(sOp) = 1 Then
            sFormula = sFormula & sOp & sParam
         Else
            sFormula = sFormula & "*" & sParam
         End If
      End If
      Cells(l4Row, "b").Value = sFormula
      sFormula = vbNullString
      sOp = vbNullString
      sParam = vbNullString
   Next l4Row
End Sub

**mark_neil2** · 06-17-2007, 09:31 PM

Hi Norm,
Thanks a lot for your effort in writing this program. I have tested it in few sample strings and it works just fine. This was great help for me.
Could you do a minor modification to this program? Instead of writing the result to column B, can you write them to column A in order by skipping one row from the last expression.
Even if this is not done, the present version does the job for me. Thanks a lot again :-)

Mark.

**mudraker** · 06-18-2007, 05:06 AM

Mark

Glad to hear the macro works.

With the required changes is their already a blank cell under each entry in Column A that the macro can just place the formula into or will the macro need to insert a blank row before it places the formula into column A

**mark_neil2** · 06-18-2007, 01:11 PM

Hi Norm,
The program already finds how many rows populated in column A and writes the results in column B in each respective row. Say, rows from A1 to A4 contain formulas. I prefer to see the results from A6 to A9 in order having A5 empty.
Thanks a bunch.

Mark.

**mudraker** · 06-19-2007, 08:08 AM

Mark

If I understand what you are after correctly this version will work.

Sub ExtractFormula()
   Dim bAdd As Boolean
   
   Dim iTxtLen As Integer
   Dim i4Len As Integer
   
   Dim l4Row As Long
   Dim lLR As Long
   
   Dim sCval As String
   Dim sFormula As String
   Dim sParam As String
   Dim sOp As String
   
   
   lLR = Cells(Rows.Count, "a").End(xlUp).Row
   
   For l4Row = 1 To lLR Step 1
      sCval = Cells(l4Row, "a")
      iTxtLen = Len(sCval)
      
      bAdd = True
      For i4Len = 1 To iTxtLen Step 1
         Select Case Mid(sCval, i4Len, 1)
         Case "(", ")"
            If Len(sOp) = 1 Then
               sFormula = sFormula & sOp & sParam
               sOp = vbNullString
            Else
               If Len(sParam) > 0 Then
                  Select Case sParam
                  Case "-", "+", "/", "*", "^"
                     sFormula = sFormula & sParam
                  Case Else
                     sFormula = sFormula & "*" & sParam
                  End Select
               End If
            End If
            sParam = vbNullString
            sFormula = sFormula & Mid(sCval, i4Len, 1)
            bAdd = True
         Case " "
            bAdd = True
            If Len(sParam) > 0 Then
               Select Case sParam
               Case "-", "+", "/", "*", "^"
                  sOp = sParam
               Case Else
                  If Len(sOp) = 1 Then
                     sFormula = sFormula & sOp & sParam
                     sOp = vbNullString
                  Else
                     If Right(sFormula, 1) = "(" Then
                        sFormula = sFormula & sParam
                     Else
                        Select Case Left(sParam, 1)
                        Case "-", "+", "/", "*", "^"
                           sFormula = sFormula & sParam
                        Case Else
                           sFormula = sFormula & "*" & sParam
                        End Select
                     End If
                  End If
               End Select
            End If
            sParam = vbNullString
         Case Else
            Select Case Mid(sCval, i4Len, 1)
            Case "["
               sParam = vbNullString
            Case "]"
               bAdd = False
            Case Else
               If bAdd = True Then
                  sParam = sParam & Mid(sCval, i4Len, 1)
               End If
            End Select
         End Select
      Next i4Len
      If Len(sParam) > 0 Then
         If Len(sOp) = 1 Then
            sFormula = sFormula & sOp & sParam
         Else
            sFormula = sFormula & "*" & sParam
         End If
      End If
      Cells(lLR + l4Row + 1, "a").Value = sFormula
      sFormula = vbNullString
      sOp = vbNullString
      sParam = vbNullString
   Next l4Row
End Sub