Solved

# seperation of a formula as shown in the sample file

Posted on 2018-03-08
Low Priority
40 Views
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
0
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")
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
``````
Formula.xlsm
0

Author Closing Comment

ID: 42493811
thnx alot sir for giving ur precious time in this post
0

LVL 35

Expert Comment

ID: 42493815
You're welcome Avinash! Glad it worked as desired.
0

## Join & Write a Comment Already a member? Login.

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…
###### Suggested Courses
Course of the Month13 days, 17 hours left to enroll

#### 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.