SOLVED

Excel Autocorrect Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-3336305%22%20slang%3D%22en-US%22%3EExcel%20Autocorrect%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3336305%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20to%20have%20Excel%20autocorrect%20the%20formatting%20of%20a%20word%20or%20phrase%20to%20italics%3F%20For%20example%20I'm%20trying%20to%20correct%20CAL%20to%20%3CEM%3ECALIFORNIA%20%3C%2FEM%3Ein%20italics.%20In%20Word%2C%20it's%20pretty%20straightforward%2C%20but%20there%20doesn't%20seem%20to%20be%20a%20way%20to%20do%20it%20in%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20also%20accept%20any%20workarounds%2C%20for%20example%20if%20CAL%20autocorrected%20to%20CALIFORNIA%2C%20and%20then%20there%20was%20a%20way%20to%20automatically%20recognize%20CALIFORNIA%20and%20make%20it%20%3CEM%3ECALIFORNIA%3C%2FEM%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3336305%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3336375%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Autocorrect%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3336375%22%20slang%3D%22en-US%22%3EHi%20Ryan.%20There%20are%20any%20number%20of%20ways%20depending%20on%20how%20you%20are%20doing%20it.%20Could%20you%20upload%20a%20workbook%20sample%20of%20what%20you%20are%20after%20%3F%20Makes%20it%20easier%20to%20get%20the%20right%20answer%20for%20you...%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3336377%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Autocorrect%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3336377%22%20slang%3D%22en-US%22%3EHi%20Cambosity%2C%20thank%20for%20the%20quick%20response!%3CBR%20%2F%3E%3CBR%20%2F%3EI%20can't%20upload%20anything%20really%2C%20confidential%20stuff.%20But%20it%20really%20is%20a%20very%20simple%20chart%20that%20I'm%20using%20as%20a%20board%20for%20a%20presentation.%20Participants%20will%20ask%20me%20to%20fill%20in%20the%20chart%20with%20various%20phrases%2C%20such%20as%20CALIFORNIA%20DREAMING%20or%20something%20similar.%20So%20I've%20created%20autocorrect%20shorthands%20such%20as%20CAL%20so%20I%20can%20better%20keep%20up%20with%20what%20they%20are%20saying.%20For%20some%20of%20the%20phrases%2C%20I%20want%20them%20to%20appear%20on%20the%20board%20in%20italics%20automatically.%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20Word%2C%20you%20can%20easily%20just%20select%20the%20word%20in%20italics%20and%20create%20a%20shorthand%20for%20that%2C%20but%20the%20same%20functionality%20doesn't%20exist%20in%20Excel.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3336948%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Autocorrect%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3336948%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1383433%22%20target%3D%22_blank%22%3E%40Ryan_Holtz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20doesn't%20exist%20in%20Excel%20since%2C%20unlike%20Word%2C%20text%20processing%20is%20not%20its%20primary%20purpose.%3C%2FP%3E%0A%3CP%3EYou%20might%20use%20VBA%20code.%20In%20the%20attached%20workbook%2C%20Sheet1%20will%20replace%20some%20abbreviations%20and%20italicize%20the%20replacement%20matching%20the%20entire%20cell.%20Sheet2%20will%20do%20the%20same%20for%20part%20of%20the%20cell.%3C%2FP%3E%0A%3CP%3EYou'll%20have%20to%20allow%20macros.%3C%2FP%3E%0A%3CP%3EThe%20code%20is%20in%20the%20worksheet%20modules%20-%20right-click%20a%20sheet%20tab%20and%20select%20View%20Code%20from%20the%20context%20menu%20to%20see%20and%20edit%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3340248%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Autocorrect%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3340248%22%20slang%3D%22en-US%22%3EThis%20is%20exactly%20what%20I%20was%20looking%20for!%20Thank%20you%20so%20much.%3CBR%20%2F%3E%3CBR%20%2F%3EApologies%20for%20being%20so%20code-illiterate%2C%20but%20is%20there%20also%20a%20way%20to%20have%20certain%20words%20be%20changed%20to%20not%20be%20italics%3F%20So%20basically%20run%20two%20VBA%20codes%20at%20the%20same%20time%2C%20one%20that%20changes%20it%20to%20italics%2C%20and%20one%20that%20changes%20it%20from%20italics%20to%20no%20italics%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3340266%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Autocorrect%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3340266%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1383433%22%20target%3D%22_blank%22%3E%40Ryan_Holtz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20current%20code%20runs%20automatically%20when%20you%20enter%20something%20in%20a%20cell.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERemoving%20Italics%20seems%20to%20be%20more%20suitable%20for%20a%20macro%20to%20be%20run%20separately.%20Or%20do%20you%20disagree%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3340301%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Autocorrect%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3340301%22%20slang%3D%22en-US%22%3EGot%20it%2C%20how%20would%20I%20create%20a%20macro%20separately%3F%20I've%20tried%20copying%20and%20pasting%20the%20code%2C%20and%20then%20altering%20it%2C%20but%20even%20if%20I%20copy%20and%20paste%20again%20with%20no%20alterations%2C%20I%20can't%20get%20it%20to%20even%20replace%20and%20italicize%20anything%2C%20which%20was%20the%20original%20purpose.%20I'm%20certainly%20way%20out%20of%20my%20depth%20here.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3340348%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Autocorrect%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3340348%22%20slang%3D%22en-US%22%3ETotally%20agreed%2C%20I%20created%20a%20new%20line%20of%20code%20that%20removes%20italics%2C%20but%20I%20just%20need%20both%20of%20them%20to%20work%20together.%20Is%20there%20a%20way%20to%20do%20that%3F%3CBR%20%2F%3E%3CBR%20%2F%3EOption%20Explicit%3CBR%20%2F%3E%3CBR%20%2F%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EDim%20arrFind%3CBR%20%2F%3EDim%20arrReplace%3CBR%20%2F%3EDim%20i%20As%20Long%3CBR%20%2F%3EIf%20Target.CountLarge%20%26gt%3B%201%20Then%20Exit%20Sub%3CBR%20%2F%3E'%20Modify%20and%20expand%20the%20arrays%20as%20needed%3CBR%20%2F%3EarrFind%20%3D%20Array(%22CAL%22%2C%20%22ORE%22)%3CBR%20%2F%3EarrReplace%20%3D%20Array(%22CALIFORNIA%20DREAMING%22%2C%20%22OREGON%20TRAIL%22)%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3EOn%20Error%20GoTo%20ExitHere%3CBR%20%2F%3EFor%20i%20%3D%200%20To%20UBound(arrFind)%3CBR%20%2F%3EIf%20Target.Value%20%3D%20arrFind(i)%20Then%3CBR%20%2F%3ETarget.Value%20%3D%20arrReplace(i)%3CBR%20%2F%3ETarget.Font.Italic%20%3D%20True%3CBR%20%2F%3EExit%20For%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%20i%3CBR%20%2F%3EExitHere%3A%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3EOption%20Explicit%3CBR%20%2F%3E%3CBR%20%2F%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EDim%20arrFind%3CBR%20%2F%3EDim%20arrReplace%3CBR%20%2F%3EDim%20i%20As%20Long%3CBR%20%2F%3EIf%20Target.CountLarge%20%26gt%3B%201%20Then%20Exit%20Sub%3CBR%20%2F%3E'%20Modify%20and%20expand%20the%20arrays%20as%20needed%3CBR%20%2F%3EarrFind%20%3D%20Array(%22WIS%22%2C%20%22FLO%22)%3CBR%20%2F%3EarrReplace%20%3D%20Array(%22WISCONSIN%20RAPIDS%22%2C%20%22FLORIDA%20ANGELS%22)%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3EOn%20Error%20GoTo%20ExitHere%3CBR%20%2F%3EFor%20i%20%3D%200%20To%20UBound(arrFind)%3CBR%20%2F%3EIf%20Target.Value%20%3D%20arrFind(i)%20Then%3CBR%20%2F%3ETarget.Value%20%3D%20arrReplace(i)%3CBR%20%2F%3ETarget.Font.Italic%20%3D%20False%3CBR%20%2F%3EExit%20For%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%20i%3CBR%20%2F%3EExitHere%3A%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3340383%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Autocorrect%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3340383%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1383433%22%20target%3D%22_blank%22%3E%40Ryan_Holtz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20new%20code%20is%20also%20a%20Worksheet_Change%20event%20procedure.%20I%20thought%20that%20you%20agreed%20with%20me%20that%20it%20would%20be%20better%20to%20use%20an%20ordinary%20macro...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3340412%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Autocorrect%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3340412%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1383433%22%20target%3D%22_blank%22%3E%40Ryan_Holtz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20combine%20them%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20Dim%20arrFind%0A%20%20%20%20Dim%20arrReplace%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20If%20Target.CountLarge%20%26gt%3B%201%20Then%20Exit%20Sub%0A%20%20%20%20'%20Modify%20and%20expand%20the%20arrays%20as%20needed%0A%20%20%20%20arrFind%20%3D%20Array(%22CAL%22%2C%20%22ORE%22)%0A%20%20%20%20arrReplace%20%3D%20Array(%22CALIFORNIA%20DREAMING%22%2C%20%22OREGON%20TRAIL%22)%0A%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20On%20Error%20GoTo%20ExitHere%0A%20%20%20%20For%20i%20%3D%200%20To%20UBound(arrFind)%0A%20%20%20%20%20%20%20%20If%20Target.Value%20%3D%20arrFind(i)%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Target.Value%20%3D%20arrReplace(i)%0A%20%20%20%20%20%20%20%20%20%20%20%20Target.Font.Italic%20%3D%20True%0A%20%20%20%20%20%20%20%20%20%20%20%20Exit%20For%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20i%0A%20%20%20%20'%20Modify%20and%20expand%20the%20arrays%20as%20needed%0A%20%20%20%20arrFind%20%3D%20Array(%22WIS%22%2C%20%22FLO%22)%0A%20%20%20%20arrReplace%20%3D%20Array(%22WISCONSIN%20RAPIDS%22%2C%20%22FLORIDA%20ANGELS%22)%0A%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20On%20Error%20GoTo%20ExitHere%0A%20%20%20%20For%20i%20%3D%200%20To%20UBound(arrFind)%0A%20%20%20%20%20%20%20%20If%20Target.Value%20%3D%20arrFind(i)%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Target.Value%20%3D%20arrReplace(i)%0A%20%20%20%20%20%20%20%20%20%20%20%20Target.Font.Italic%20%3D%20False%0A%20%20%20%20%20%20%20%20%20%20%20%20Exit%20For%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20i%0AExitHere%3A%0A%20%20%20%20Application.EnableEvents%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

Is there a way to have Excel autocorrect the formatting of a word or phrase to italics? For example I'm trying to correct CAL to CALIFORNIA in italics. In Word, it's pretty straightforward, but there doesn't seem to be a way to do it in Excel.

 

I'd also accept any workarounds, for example if CAL autocorrected to CALIFORNIA, and then there was a way to automatically recognize CALIFORNIA and make it CALIFORNIA.

 

Any help would be greatly appreciated!

8 Replies
Hi Ryan. There are any number of ways depending on how you are doing it. Could you upload a workbook sample of what you are after ? Makes it easier to get the right answer for you...
Hi Cambosity, thank for the quick response!

I can't upload anything really, confidential stuff. But it really is a very simple chart that I'm using as a board for a presentation. Participants will ask me to fill in the chart with various phrases, such as CALIFORNIA DREAMING or something similar. So I've created autocorrect shorthands such as CAL so I can better keep up with what they are saying. For some of the phrases, I want them to appear on the board in italics automatically.

In Word, you can easily just select the word in italics and create a shorthand for that, but the same functionality doesn't exist in Excel.
best response confirmed by Ryan_Holtz (New Contributor)
Solution

@Ryan_Holtz 

This doesn't exist in Excel since, unlike Word, text processing is not its primary purpose.

You might use VBA code. In the attached workbook, Sheet1 will replace some abbreviations and italicize the replacement matching the entire cell. Sheet2 will do the same for part of the cell.

You'll have to allow macros.

The code is in the worksheet modules - right-click a sheet tab and select View Code from the context menu to see and edit it.

This is exactly what I was looking for! Thank you so much.

Apologies for being so code-illiterate, but is there also a way to have certain words be changed to not be italics? So basically run two VBA codes at the same time, one that changes it to italics, and one that changes it from italics to no italics?

@Ryan_Holtz 

The current code runs automatically when you enter something in a cell.

 

Removing Italics seems to be more suitable for a macro to be run separately. Or do you disagree?

Totally agreed, I created a new line of code that removes italics, but I just need both of them to work together. Is there a way to do that?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim arrFind
Dim arrReplace
Dim i As Long
If Target.CountLarge > 1 Then Exit Sub
' Modify and expand the arrays as needed
arrFind = Array("CAL", "ORE")
arrReplace = Array("CALIFORNIA DREAMING", "OREGON TRAIL")
Application.EnableEvents = False
On Error GoTo ExitHere
For i = 0 To UBound(arrFind)
If Target.Value = arrFind(i) Then
Target.Value = arrReplace(i)
Target.Font.Italic = True
Exit For
End If
Next i
ExitHere:
Application.EnableEvents = True
End Sub

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim arrFind
Dim arrReplace
Dim i As Long
If Target.CountLarge > 1 Then Exit Sub
' Modify and expand the arrays as needed
arrFind = Array("WIS", "FLO")
arrReplace = Array("WISCONSIN RAPIDS", "FLORIDA ANGELS")
Application.EnableEvents = False
On Error GoTo ExitHere
For i = 0 To UBound(arrFind)
If Target.Value = arrFind(i) Then
Target.Value = arrReplace(i)
Target.Font.Italic = False
Exit For
End If
Next i
ExitHere:
Application.EnableEvents = True
End Sub

@Ryan_Holtz 

Your new code is also a Worksheet_Change event procedure. I thought that you agreed with me that it would be better to use an ordinary macro...

@Ryan_Holtz 

If you want to combine them:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim arrFind
    Dim arrReplace
    Dim i As Long
    If Target.CountLarge > 1 Then Exit Sub
    ' Modify and expand the arrays as needed
    arrFind = Array("CAL", "ORE")
    arrReplace = Array("CALIFORNIA DREAMING", "OREGON TRAIL")
    Application.EnableEvents = False
    On Error GoTo ExitHere
    For i = 0 To UBound(arrFind)
        If Target.Value = arrFind(i) Then
            Target.Value = arrReplace(i)
            Target.Font.Italic = True
            Exit For
        End If
    Next i
    ' Modify and expand the arrays as needed
    arrFind = Array("WIS", "FLO")
    arrReplace = Array("WISCONSIN RAPIDS", "FLORIDA ANGELS")
    Application.EnableEvents = False
    On Error GoTo ExitHere
    For i = 0 To UBound(arrFind)
        If Target.Value = arrFind(i) Then
            Target.Value = arrReplace(i)
            Target.Font.Italic = False
            Exit For
        End If
    Next i
ExitHere:
    Application.EnableEvents = True
End Sub