Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse formula
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse formula
Don,
I wrote a function to extract arguments from a formula (http://www.vangelder.co.nz/excel/index.html) You might be able to change the line which reads: Application.International(xlListSeparator) to use a "+" instead. Rob "Don Guillett" wrote in message ... 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 -- Don Guillett SalesAid Software |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parse formula
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 -- Don Guillett SalesAid Software "Rob van Gelder" wrote in message ... Don, I wrote a function to extract arguments from a formula (http://www.vangelder.co.nz/excel/index.html) You might be able to change the line which reads: Application.International(xlListSeparator) to use a "+" instead. Rob "Don Guillett" wrote in message ... 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 -- Don Guillett SalesAid Software |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parse Text | Excel Discussion (Misc queries) | |||
How to parse data | Excel Discussion (Misc queries) | |||
Parse from the Right | Excel Worksheet Functions | |||
How do I capitalize and parse in the same formula? | Excel Worksheet Functions | |||
Parse | Excel Programming |