seperation of a formula as shown in the sample file

in my sheet i have formulas i have just replace the = to with # so the formula will not work
and now what i want to separate it so i can check wheather it is correct or not
now what i want is to split the formula into multiple cell
= to separate cell
IF to seperate cell
all referred cell to spearate cell
see the attachment u will understand
Avinash SinghAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...
In the attached, click the button called "Split Formulas" on Sheet1 to run the code. The code will create a sheet called "Formulas" with the desired output.

Sub SplitFormulas()
Dim sws As Worksheet, dws As Worksheet
Dim lr As Long, fLen As Long, i As Long, ii As Long, j As Long
Dim Rng As Range, cell As Range
Dim x()

Application.ScreenUpdating = False

Set sws = Sheets("Sheet1")

On Error Resume Next
Set dws = Sheets("Formulas")
On Error GoTo 0

If dws Is Nothing Then
    Set dws = Worksheets.Add(after:=sws)
    dws.Name = "Formulas"
End If

lr = sws.Cells(Rows.Count, 9).End(xlUp).Row
Set Rng = sws.Range("I1:I" & lr)

For Each cell In Rng
    If Len(cell.Value) >= fLen Then
        fLen = Len(cell.Value)
    End If
Next cell
ReDim x(1 To lr, 1 To fLen)

For Each cell In Rng
    ii = ii + 1
    For i = 1 To Len(cell.Value)
        j = j + 1
        If (Not Mid(cell.Value, i, 1) Like [0-9]) And (Not Mid(cell.Value, i, 1) Like "[A-Z]") Then
            x(ii, j) = Mid(cell.Value, i, 1)
            Do While Mid(cell.Value, i, 1) Like "[0-9]" Or Mid(cell.Value, i, 1) Like "[A-Z]"
                x(ii, j) = x(ii, j) & Mid(cell.Value, i, 1)
                i = i + 1
            i = i - 1
        End If
    Next i
    j = 0
Next cell
dws.Range("A1").Resize(ii, UBound(x, 2)).Value = x
Application.ScreenUpdating = True

End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Avinash SinghAuthor Commented:
thnx alot sir for giving ur precious time in this post
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Avinash! Glad it worked as desired.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.