Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 65
  • Last Modified:

How to pull text from inside a formula in Excel 2010?

I have some formulas that are on the wrong rows.
For example, the formula is on row 782 should say
=IF('MS Only Alt & Kept Commands'!M782<>'MS Only Alt & Kept Commands'!J782,'MS Only Alt & Kept Commands'!$J782,"")
but actually says
=IF('MS Only Alt & Kept Commands'!M781<>'MS Only Alt & Kept Commands'!J781,'MS Only Alt & Kept Commands'!$J781,"")

Is there a way to pull 781 out of the formula and show it.?
0
Alex Campbell
Asked:
Alex Campbell
  • 2
  • 2
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
You can do such a thing with the help of VBA.
0
 
Martin LissOlder than dirtCommented:
I don't know what you mean by "show it" but this will update the formulas on row 782.

Sub ReplaceRowNumber()
Dim cel As Range
With ActiveSheet.Rows(782)
    For Each cel In .Cells
        cel.Formula = Replace(cel.Formula, "781", "782")
    Next
End With
End Sub

Open in new window

0
 
Alex CampbellAuthor Commented:
It would show in a separate cell.
I want to do that since there could be a number of rows that are mismatched.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Saqib Husain, SyedEngineerCommented:
This formula

=chkrowfrmla("B781")

with this code

Function chkrowfrmla(frmla As Range)
If InStr(frmla.Formula, frmla.Row) > 0 Then chkrowfrmla = "" Else chkrowfrmla = "Mismatch"
End Function

will return "Mismatch" wherever the row number is not found in the formula.

This code will not catch if the row is 24 but the formula says 243
0
 
Martin LissOlder than dirtCommented:
I think this update to Syed's code solves the problem he refers to. (Edit: changed code to remove "M")

Function chkrowfrmla(frmla As Range)

Dim strParts() As String
Dim strTemp As String
strTemp = Replace(frmla.Formula, "<", "!")
strParts = Split(strTemp, "!")
If UBound(strParts) < 1 Then
    chkrowfrmla = ""
    Exit Function
End If
strTemp = Replace(strParts(1), "M", "")
If strTemp = frmla.Row Then
    chkrowfrmla = ""
Else
    chkrowfrmla = "Mismatch"
End If
End Function

Open in new window

0
 
Alex CampbellAuthor Commented:
This works just fine. Thanks
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now