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
Formula.xlsm
Avinash SinghAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor 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")
dws.Cells.Clear
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)
        Else
            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
            Loop
            i = i - 1
        End If
    Next i
    j = 0
Next cell
dws.Range("A1").Resize(ii, UBound(x, 2)).Value = x
dws.Activate
Application.ScreenUpdating = True

End Sub

Open in new window

Formula.xlsm
0
 
Avinash SinghAuthor Commented:
thnx alot sir for giving ur precious time in this post
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Avinash! Glad it worked as desired.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.