seperation of a formula as shown in the sample file

Posted on 2018-03-08
Low Priority
Last Modified: 2018-03-09
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
Question by:Avinash Singh
  • 2
LVL 35

Accepted Solution

Subodh Tiwari (Neeraj) earned 1000 total points
ID: 42493002
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


Author Closing Comment

by:Avinash Singh
ID: 42493811
thnx alot sir for giving ur precious time in this post
LVL 35

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 42493815
You're welcome Avinash! Glad it worked as desired.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Join & Write a Comment

In this post, we will learn to set up the Group Naming policy and will see how it is going to impact the Display Name and the Email addresses of the Group.
If Skype for Business came with your office 2016 or office 365 installation, you may find that it's almost impossible to either disable or remove it. The application will often launch with each start of Windows, even when explicitly configured not t…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question