HOW TO REPLACE TEXT AUTOMATICALLY

  • Thread starter Thread starter Yuanhang
  • Start date Start date
Y

Yuanhang

AS THE TITLE SAYS, I WANNA REPLACE SOME LONG WORDS INTO SHORT ONES, LIKE
"COMPANY" TO "CO.", "LIMITED" TO "LTD.". I KNOW I CAN DO THIS ONE BY ONE
MANUALLY. HOWEVER, IS THERE ANYWAY THAT COULD DO THIS AUTOMATICALLY WHENEVER
I UPDATE MY DATA? THANK YOU.
 
Click TOOLS in the menu and select AUTOCORRECT.

On the AUTOCORRECT tab in the REPLACE field, enter the word you want to
replace and in the WITH field the word or abbreviation you want to replace
the word with.

Click ADD

Repeat as many times as necessary.
 
THANK U VERY MUCH FOR SUCH A QUICK ANSWER. IT WORKS. HOWEVER, IT COULDN'T BE
REFRESHED UNTIL I CLICK ENTER AT EACH CELL. IS IT BECAUSE MY VERSION OF EXCEL
TOO OLD (EXCEL 2000)?
LET'S SAY, THERE IS A CELL SAYS, "ABC COMPANY". AND I WANT IT TO BE LIKE
"ABC CO." SO I INPUT "COMPANY" FOLLOWING "REPLACE" AND TYPE "CO." INTO THE
"WITH" FIELD AS WHAT U TOLD ME TO DO. THEN I CLICK "ADD" AND "OK".
BUT THE TEXT DOESN'T CHANGE ITSELF UNLESS I SELECT THAT CELL AND PRESS
SPACE. IN THIS CASE, THE AUTOCORRECT DOESN'T HELP A LOT. ANYWAY TO IMPROVE
IT? THANK YOU.
 
Please drop the CAPS. Very hard on the ears and difficult to read.

Autocorrect only works on newly entered text, not on existing text unless you go
to each cell and hit F2>Enter

For existing text why not select all the cells then Edit>Replace

what: Company

with: Co.


Gord Dibben MS Excel MVP
 
Sorry about the CAPs.

There're two problems.

First, I wanna replace a lot of different words and I update my data often.
Because of that, I couldn't afford to replace everything everytime I update
the spreadsheet with new data.

Second, there're some words in the cell are part of the text. Hitting the F2
button seems only work when the text that in the cell is just the whole thing
that I wanna change. Let's say, if the text in the cell is "ABC Import &
Export Co." and I want it to be "ABC Imp. & Exp. Co.". By just hitting F2
doesn't change the words in the middle.

Is there anything I do wrong? Please let me know.

Thank you.
 
Autocorrect is not the best tool for this job.

How many words are you talking about?

5, 10, many?


Gord
 
At least 10. And I have to add new data everyday. So that's why I don't wanna
repeat those works day after day. So which fuction or feature might be fit?
Should I record a Mico. for that?
 
I'd use a macro.

Start a new workbook--its only purpose is to hold the macro and the list of
words/phrases to be deleted and the list to be used for the replacement.

Then put your list in Column A and column B of sheet1 of that workbook.

Put this macro in that workbook's project in a general module--not behind a
worksheet, not behind ThisWorkbook.

Option Explicit
Sub MassChanges()

Dim myCell As Range
Dim myList As Range

With ThisWorkbook.Worksheets("Sheet1")
Set myList = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With ActiveSheet
For Each myCell In myList.Cells
.Cells.Replace _
what:=myCell.Value, _
replacement:=myCell.Offset(0, 1).Value, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
MatchCase:=False
Next myCell
End With

End Sub

I used xlwhole--to match the entire cell and I used matchcase:=false. You may
want to change these.

After you've done this, you can save this workbook.

Whenever you need to ammend the list, just type over the entries or add new or
delete old.

When ever you need to run the macro, open this workbook.
Open the imported file.
With that imported workbook's worksheet active, hit alt-f8 and run the macro.
 
Dave/Yuanhang

I think xlWhole should be xlPart

Data is: ABC import and export company

Change to: ABC imp and exp co


Gord
 
One of the (many) problems that comes with not reading the entire thread <bg>.

Thanks for the correction.
 
Dave and Gord and also Kevin:
Thanks u all for the help. I am new for Macro. So I don't know whether those
codes work or not. I am going to learn it at home first. Thank you guys
again.
 
Back
Top