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?
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)
0
Ryan ChongCommented:
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

0

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 ChongCommented:
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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Ryan ChongCommented:
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

0
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
0
BiopsychAuthor Commented:
Ryan Chong:

Thank you very much. Perfect answer. Very helpful.
0
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.
0
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.

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.