Avatar of Alex Campbell
Alex Campbell
Flag for United States of America asked on

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.?
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Alex Campbell

8/22/2022 - Mon
Saqib Husain

You can do such a thing with the help of VBA.
Martin Liss

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")
End With
End Sub

Open in new window

Alex Campbell

It would show in a separate cell.
I want to do that since there could be a number of rows that are mismatched.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Saqib Husain

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Martin Liss

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 = ""
    chkrowfrmla = "Mismatch"
End If
End Function

Open in new window

Alex Campbell

This works just fine. Thanks