Create and Copy AutoCorrect List Items in Excel

To save time, create AutoCorrect list entries for words, phrases, and even symbols that you type frequently. Then, type a short code, and Excel automatically changes it to the full text. See how to create an entry, then print a list of all your entries, and copy them to a different computer, using the AutoCorrect macros below.

Create Excel AutoCorrect Entries

The programs in Microsoft Office share a common list of AutoCorrect entries. Any entries that you create or change in Excel, will also be available in Word, Outlook, and PowerPoint.

To see the AutoCorrect list:

  • On the Excel Ribbon, click the File tab, and then click Options
  • Click the Proofing category, and then click the AutoCorrect Options button.

To add a text entry:

  • In the Replace box, type a short code that will be easy to remember.
  • In the With box, type the full word or phrase that will replace the short code, after you type it.

For example, I entered the short code: .dd

When I type that on a worksheet, it will be replaced by my full name: Debra Dalgleish

AutoCorrect entry for name

Create AutoCorrect Symbols

In the AutoCorrect Options window, there is a tab for Math AutoCorrect. Unfortunately, those shortcuts don't work in Excel.

However, you can copy the symbols from that tab, and create regular AutoCorrect entries for them.

This animated screen shot shows the steps - type a short code, copy and paste a symbol from the Math tab, then use the short code on your worksheet, any time you need it.

autocorrectsymbols03

Print AutoCorrect List Entries

If you'd like to see everything that's in your AutoCorrect list, run this macro. It was written long ago by Dana DeLouis, and he shared it in the Excel newsgroups.

I added a line to insert a new sheet, so you don't accidentally overwrite anything. The macro lists the short codes (Replace) items in column A, and the full text (With) in column B.

AutoCorrect List

Copy this macro, and paste it into a regular code module.

Sub AutoCorrectEntries_Display()
'// Dana DeLouis
'// Backup AutoCorrect to Worksheet
'2018-01-30 added line to insert new sheet
   Dim ACE
   ACE = Application.AutoCorrect.ReplacementList
   Sheets.Add
   Range(Cells(1), Cells(UBound(ACE), 2)) = ACE
   Columns("A:B").AutoFit
End Sub

Edit the AutoCorrect List Entries

After the AutoCorrect list is created on a worksheet, you can manually edit it, before you import the list to a different computer.

For example,

  • add new codes and replacement text
  • modify the replacement text for personal entries that you previously created
  • delete any personal entries that won't be needed on the other computer

Or, just leave the list as is, then save and close the workbook.

Copy AutoCorrect List Entries

After you create your list of AutoCorrect entries, use the following macro (also by Dana DeLouis)  to add those entries on a different computer.

  • Open the workbook with the list,
  • Activate the sheet that has the exported list of AutoCorrect items.
  • Run the macro below, to add those items to the new computer's list.

What the macro does:

  • adds any new items
  • overwrites any matching codes on the new computer
  • does NOT remove any existing entries that are not in the imported list

Copy this macro, and paste it into a regular code module.

Sub AutoCorrectEntries_Add()
'// Dana DeLouis
'// Add AutoCorrect entries.
'// Column A -> Wrong   Word
'// Column B -> Correct Word
   Dim rng As Range
   With Application.AutoCorrect
      For Each rng In Columns(1) _
      .SpecialCells(xlConstants, _
          xlTextValues).Cells
         .AddReplacement rng, rng.Offset(0, 1)
      Next
   End With
End Sub

_____________________________________

Create and Copy AutoCorrect List Items http://blog.contextures.com/

____________________

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *