Parse formula

  • Thread starter Thread starter Don Guillett
  • Start date Start date
D

Don Guillett

Also posted on L list

How can I turn this

=SUM(IU6+IU16+IU28+IU57*2+IU99*2)

into
=iu6 better yet = a6 which is text
=iu16
=iu28
=iu57*2
=iu99*2

preferable turn the last two into
=a57 (which is TEXT) and *2 in text
like where a57 is item4 in text
=item4 *2
 
Rob, Thanks for your reply. I have not had a chance yet to try your code.
Here is one I developed with help on the 1st part by Damon Longworth on the
L list. From this
=SUM(IU9+IU23*2+IU30*4+IU40*2+IU55*2+IU100*8)
to this where this text was in the A column for each formula, depending on
row.
277-4L BALLAST
KLFIT/SA/4*2
4FT F32/841LAMP30*4
2x4-LENS/KSH-23*2
# 131'2X4-TROFFER FIX CAN*2
120 76 W BALLASTIU100*8


using
Sub ParseFormula()
For Each mformula In Range("d138:bc138")
MYformula = mformula.Formula
MYformula = Mid(MYformula, WorksheetFunction.Find("(", MYformula) + 1)
iLength = WorksheetFunction.Find("+", MYformula)
mformula.Offset(3, 0) = Mid(MYformula, 1, iLength - 1)
MYformula = Mid(MYformula, iLength + 1)
For i = 1 To Len(MYformula) - _
Len(WorksheetFunction.Substitute(MYformula, "+", ""))
iLength = WorksheetFunction.Find("+", MYformula)
mformula.Offset(i + 3, 0) = Mid(MYformula, 1, iLength - 1)
MYformula = Mid(MYformula, iLength + 1)
Next
mformula.Offset(i + 3, 0) = Left(MYformula, Len(MYformula) - 1)

'me below
mr = mformula.Row
mc = mformula.Column
On Error Resume Next
For Each c In Range(Cells(mr + 3, mc), Cells(mr + 10, mc))
If InStr(c, "*") Then
x = InStr(c, "*")
storit = Right(c, Right(c, Len(c) - x))
c.Value = "=A" & Mid(c, 3, Len(c) - x + 1)
c.Value = c & storit
Else
c.Value = "=a" & Mid(c, 3, Len(c) - 2)
End If
Next c
Next mformula
End Sub
 
Back
Top