Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO REPLACE TEXT AUTOMATICALLY
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO REPLACE TEXT AUTOMATICALLY
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. -- Kevin Backmann "Yuanhang" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO REPLACE TEXT AUTOMATICALLY
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. "Kevin B" wrote: 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. -- Kevin Backmann "Yuanhang" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO REPLACE TEXT AUTOMATICALLY
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 F2Enter For existing text why not select all the cells then EditReplace what: Company with: Co. Gord Dibben MS Excel MVP On Wed, 5 Dec 2007 09:28:00 -0800, Yuanhang wrote: 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. "Kevin B" wrote: 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. -- Kevin Backmann "Yuanhang" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO REPLACE TEXT AUTOMATICALLY
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. "Gord Dibben" wrote: 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 F2Enter For existing text why not select all the cells then EditReplace what: Company with: Co. Gord Dibben MS Excel MVP On Wed, 5 Dec 2007 09:28:00 -0800, Yuanhang wrote: 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. "Kevin B" wrote: 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. -- Kevin Backmann "Yuanhang" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO REPLACE TEXT AUTOMATICALLY
Autocorrect is not the best tool for this job.
How many words are you talking about? 5, 10, many? Gord On Wed, 5 Dec 2007 10:17:01 -0800, Yuanhang wrote: 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. "Gord Dibben" wrote: 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 F2Enter For existing text why not select all the cells then EditReplace what: Company with: Co. Gord Dibben MS Excel MVP On Wed, 5 Dec 2007 09:28:00 -0800, Yuanhang wrote: 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. "Kevin B" wrote: 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. -- Kevin Backmann "Yuanhang" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO REPLACE TEXT AUTOMATICALLY
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? "Gord Dibben" wrote: Autocorrect is not the best tool for this job. How many words are you talking about? 5, 10, many? Gord On Wed, 5 Dec 2007 10:17:01 -0800, Yuanhang wrote: 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. "Gord Dibben" wrote: 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 F2Enter For existing text why not select all the cells then EditReplace what: Company with: Co. Gord Dibben MS Excel MVP On Wed, 5 Dec 2007 09:28:00 -0800, Yuanhang wrote: 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. "Kevin B" wrote: 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. -- Kevin Backmann "Yuanhang" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO REPLACE TEXT AUTOMATICALLY
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. Yuanhang wrote: 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? "Gord Dibben" wrote: Autocorrect is not the best tool for this job. How many words are you talking about? 5, 10, many? Gord On Wed, 5 Dec 2007 10:17:01 -0800, Yuanhang wrote: 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. "Gord Dibben" wrote: 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 F2Enter For existing text why not select all the cells then EditReplace what: Company with: Co. Gord Dibben MS Excel MVP On Wed, 5 Dec 2007 09:28:00 -0800, Yuanhang wrote: 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. "Kevin B" wrote: 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. -- Kevin Backmann "Yuanhang" wrote: 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. -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO REPLACE TEXT AUTOMATICALLY
ps...
If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Yuanhang wrote: <<snipped -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO REPLACE TEXT AUTOMATICALLY
Dave/Yuanhang
I think xlWhole should be xlPart Data is: ABC import and export company Change to: ABC imp and exp co Gord On Wed, 05 Dec 2007 13:34:00 -0600, Dave Peterson wrote: 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. Yuanhang wrote: 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? "Gord Dibben" wrote: Autocorrect is not the best tool for this job. How many words are you talking about? 5, 10, many? Gord On Wed, 5 Dec 2007 10:17:01 -0800, Yuanhang wrote: 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. "Gord Dibben" wrote: 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 F2Enter For existing text why not select all the cells then EditReplace what: Company with: Co. Gord Dibben MS Excel MVP On Wed, 5 Dec 2007 09:28:00 -0800, Yuanhang wrote: 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. "Kevin B" wrote: 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. -- Kevin Backmann "Yuanhang" wrote: 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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO REPLACE TEXT AUTOMATICALLY
One of the (many) problems that comes with not reading the entire thread <bg.
Thanks for the correction. Gord Dibben wrote: Dave/Yuanhang I think xlWhole should be xlPart Data is: ABC import and export company Change to: ABC imp and exp co Gord On Wed, 05 Dec 2007 13:34:00 -0600, Dave Peterson wrote: 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. Yuanhang wrote: 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? "Gord Dibben" wrote: Autocorrect is not the best tool for this job. How many words are you talking about? 5, 10, many? Gord On Wed, 5 Dec 2007 10:17:01 -0800, Yuanhang wrote: 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. "Gord Dibben" wrote: 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 F2Enter For existing text why not select all the cells then EditReplace what: Company with: Co. Gord Dibben MS Excel MVP On Wed, 5 Dec 2007 09:28:00 -0800, Yuanhang wrote: 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. "Kevin B" wrote: 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. -- Kevin Backmann "Yuanhang" wrote: 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. -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO REPLACE TEXT AUTOMATICALLY
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. "Dave Peterson" wrote: ps... If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Yuanhang wrote: <<snipped -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically replace a formula with its value? | Excel Discussion (Misc queries) | |||
Can I replace a ' at the beginning of a text cell using Replace | Excel Discussion (Misc queries) | |||
replace text string automatically in cell | Excel Discussion (Misc queries) | |||
Automatically replace cell value with next in list... | Excel Worksheet Functions | |||
Can I automatically replace data when importing a text file? | New Users to Excel |