Custom Cell Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2526347%22%20slang%3D%22en-US%22%3ECustom%20Cell%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2526347%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20wanting%20to%20custom%20format%20a%20cell%20so%20when%20dta%20is%20entered%2C%20it%20converts%20to%20show%20a%20different%20format%20as%20such%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EData%20entered%3A%20A23456789%3C%2FP%3E%3CP%3EDesired%20display%3A%20A23%20456%20789%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEssentially%20I%20would%20like%20a%20space%20every%20third%20character.%26nbsp%3B%20All%20data%20will%20begin%20with%20a%20letter%20followed%20by%208%20numbers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20what%20I%20want%20to%20do%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2526347%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2526890%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20Cell%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2526890%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1098183%22%20target%3D%22_blank%22%3E%40CReesor%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECustom%20number%20formats%20only%20apply%20to%20numbers%20and%20dates%2C%20not%20to%20text%20values.%20You'd%20have%20to%20use%20VBA%20code%20to%20do%20what%20you%20want.%20Let's%20say%20you%20want%20to%20insert%20spaces%20in%20column%20A.%20from%20cell%20A2%20down.%3C%2FP%3E%0A%3CP%3ERight-click%20the%20sheet%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20'View%20Code'%20from%20the%20context%20menu.%3C%2FP%3E%0A%3CP%3ECopy%20the%20following%20code%20into%20the%20worksheet%20module%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20Dim%20rng%20As%20Range%0A%20%20%20%20Dim%20s%20As%20String%0A%20%20%20%20Dim%20t%20As%20String%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20If%20Not%20Intersect(Range(%22A2%3AA%22%20%26amp%3B%20Rows.Count)%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20%20%20%20%20For%20Each%20rng%20In%20Intersect(Range(%22A2%3AA%22%20%26amp%3B%20Rows.Count)%2C%20Target)%0A%20%20%20%20%20%20%20%20%20%20%20%20s%20%3D%20rng.Value%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20s%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20If%20InStr(s%2C%20%22%20%22)%20%3D%200%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20t%20%3D%20%22%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20For%20i%20%3D%20Len(s)%20-%202%20To%201%20Step%20-3%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20t%20%3D%20Mid(s%2C%20i%2C%203)%20%26amp%3B%20%22%20%22%20%26amp%3B%20t%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Next%20i%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20If%20Len(s)%20Mod%203%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20t%20%3D%20Left(s%2C%20Len(s)%20Mod%203)%20%26amp%3B%20%22%20%22%20%26amp%3B%20t%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20rng.Value%20%3D%20t%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20Next%20rng%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20%20%20%20%20%20Application.ScreenUpdating%20%3D%20True%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ESwitch%20back%20to%20Excel.%3C%2FP%3E%0A%3CP%3ESave%20the%20workbook%20as%20a%20macro-enabled%20workbook%20(.xlsm)%3C%2FP%3E%0A%3CP%3EMake%20sure%20that%20you%20allow%20macros%20when%20you%20open%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

I am wanting to custom format a cell so when dta is entered, it converts to show a different format as such:

 

Data entered: A23456789

Desired display: A23 456 789

 

Essentially I would like a space every third character.  All data will begin with a letter followed by 8 numbers.

 

Is what I want to do possible?

 

1 Reply

@CReesor 

Custom number formats only apply to numbers and dates, not to text values. You'd have to use VBA code to do what you want. Let's say you want to insert spaces in column A. from cell A2 down.

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim s As String
    Dim t As String
    Dim i As Long
    If Not Intersect(Range("A2:A" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("A2:A" & Rows.Count), Target)
            s = rng.Value
            If s <> "" Then
                If InStr(s, " ") = 0 Then
                    t = ""
                    For i = Len(s) - 2 To 1 Step -3
                        t = Mid(s, i, 3) & " " & t
                    Next i
                    If Len(s) Mod 3 Then
                        t = Left(s, Len(s) Mod 3) & " " & t
                    End If
                    rng.Value = t
                End If
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook (.xlsm)

Make sure that you allow macros when you open it.