In the excel file attached, column A in worksheet labelled "orginal"
has random information of text.
I would like to replace certain specific text within that column A
with that of info as provided in the "replace" worksheet without
deleting any other info from column A.
Show me the formula/function that would best serve my needs.
File attached here at
http://www.geocities.com/statsmath1905/TEST.xls

The SUBSTITUTE function in Excel and Edit > Replace both perform the
basic function you need. Because you have a large number of
replacements you wish to make, there are several ways you can do it.
The best for you will depend opn how comfortable you are with VBA,
among other things.

Using no VBA at all, we can build up the answer just using the
SUBSTITUTE function. Probably the simplest way to do this is to use
one column for each substitution. The formula in cell B1 is
=SUBSTITUTE(A1,'REPLACE INFO'!$A$1,'REPLACE INFO'!$B$1), the formula
in cell C1 is =SUBSTITUTE(B1,'REPLACE INFO'!$A$2,'REPLACE INFO'!$B$2),
and so on until you've used all the replacement info. Fill these
formulas down for all your data, and the last column is the result.

You can condense this somewhat by putting several of these together in
one step (though it's rather messy), e.g.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,'REPLACE
INFO'!$A$1,'REPLACE INFO'!$B$1),'REPLACE INFO'!$A$2,'REPLACE
INFO'!$B$2),'REPLACE INFO'!$A$3,'REPLACE INFO'!$B$3),'REPLACE
INFO'!$A$4,'REPLACE INFO'!$B$4). As you can see this is rather
confusing. Also, you can only nest up to seven levels of functions in
an Excel formula. Since you have 50 replacements to do you'd still
need at least 8 columns of formulas. This might nevertheless be better
than 50 columns of formulas.

If you're happy to use a bit of simple VBA then it can be done much
more cleanly, especially if you don't particularly want to preserve
the original data in A (just the transformed data). Also, you don't
have to change anything if the number of replacements you want to do
changes. This may or may not be an advantage depending on your
application.

The following function will do the trick (some lines may be broken in
two when posting; sorry, not much I can do about that). Assumptions:
Column A contains the data to be transformed and nothing else, and the
'replace info' sheet contains pairs in columns A and B with an empty
cell in column A at the end of the list (and not before). These
assumptions can be relaxed if necessary (at the cost of complicating
the code a little), but most likely they will be OK. It's also a very
small change if you want to keep the original as well as the
transformed data, so let me know if any changes are needed.

Sub ReplaceText()
    Dim Original As Worksheet, RData As Worksheet, CurCell As Range
    Dim FText As String, RText As String
    
    Set Original = ThisWorkbook.Worksheets("ORIGINAL")
    Set RData = ThisWorkbook.Worksheets("REPLACE INFO")
    Set CurCell = RData.Range("A1")
    
    Original.Activate
    Original.Range("A1").Select
    Do While CurCell.Formula <> ""
        FText = CurCell.Text
        RText = CurCell.Offset(0, 1).Text
        Original.Range("A:A").Replace FText, RText, xlPart, , True
' Change last parameter to False if you want case-insensitive search
        Set CurCell = CurCell.Offset(1, 0)
    Loop
End Sub


You can either put this in as a macro and run it via either Tools >
Macro > Macros or a keyboard shortcut, or you can create a button and
make this the procedure that is run when the button is clicked. Let me
know if you're not sure how to do either of these things.

Have fun,

manuka-ga