0

So let's say I have a formula "=A1/5" and I want to change it to "=A1/sqrt(5" i.e. inserting something at the second last position.

(subsequently I can do a simple = cell&")" to complete the formula).

I managed to do it but I took a very lengthy route, so wonder if anyone has idea how to quickly accomplish this.

My approach now:

  1. Deformularize by replacing "=" with "" aka nothing.

  2. Extracting the left part and rightmost part of the string separately by =LEFT(cell,LEN(cell)-1) and =RIGHT(cell,1)

  3. For the rightmost part extracted (which is a single digit number), I simply do a ="sqrt("&cell
  4. I then concatenate together a "=", the extracted left part and the amended right part.

this works, but as you can see, it took quite a bit of work.

1

This tiny macro will do the work for you:

Sub formulaFixer()
    Dim s As String
    s = ActiveCell.Formula
    arr = Split(s, "/")
    ActiveCell.Formula = arr(0) & "/SQRT(" & arr(1) & ")"
End Sub

It will convert:

=A1/5

into:

=A1/SQRT(5)

In fact, it can convert =something/something else into =something/SQRT(something else)

We can loop it over several cells, if necessary.

|improve this answer|||||
  • Thanks, I will try it out! – y chung Mar 26 at 19:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.