In Excel how would I replace a specific number everytime it appears in a column?

I have an excel file that contains a column with various numbers. The number "100" appears many times.
I need to replace "100" every time it appears in this column with "-1"
All other numbers must remain as is. I have attached an example.example-EE.xlsx
Please advise.Thank you
BiopsychAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Katie PierceCommented:
There is perhaps a macro you can run to accomplish such a task, but unfortunately I can't be of any help there. For a more manual option, you can run a helper column that gives you the results you're looking for.

So, if your data were in column A, then in column B you could have:

=IF(A1=100,-1,A1)
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
depends on when and where you want to replace the text. If you do not want to create another column and wish to replace for the original values, you can try use this macro scripts accordingly:
Sub test()
    Range("B:B").Replace "100", "-1"
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
in case you need to do it dynamically and instantly, you can also create a sub like this in target worksheet's Change event:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 Then
        For Each r In Target
            r.Replace "100", "-1"
        Next
    End If
End Sub

Open in new window

28735450.xlsm
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
ooops, sorry, which it can be simplified as:
Private Sub Worksheet_Change(ByVal Target As Range)
'The column you wish to replace with... 2 = Column B, etc
    If Target.Column = 2 Then
        Target.Replace "100", "-1"
    End If
End Sub

Open in new window

Saurabh Singh TeotiaCommented:
In C Column you can simply use this formula...

=SUBSTITUTE(B2,100,-1)

Open in new window


Enclosed your workbook for reference...

Saurabh...
example-EE.xlsx
BiopsychAuthor Commented:
Ryan Chong:

Thank you very much. Perfect answer. Very helpful.
BiopsychAuthor Commented:
Thanks for all your solutions.  I did not want to create a new column. I needed to replace the "100"s with "-1" in the same column.
 Sorry that my post was not as clear as I had thought.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.