Forum Discussion

Otto757's avatar
Otto757
Copper Contributor
Jan 19, 2021

Custom Format Textstring (alphanumeric entry, numbers and letters)

I want the cell to provide proper spacing at specific points in a value.
If I put: 12ABC3456789012

I want to see:  12A BC 34567 89012

 

How do I write the Custom Number Formatting?

I'm a novice and unfamiliar with Macros, but those have been suggested as a possible solution.  I don't believe Data Validation would help me here.

excel
Formulas and Functions

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 19, 2021

    Otto757 

    Since it is a text string, you cannot use a custom number format.

     

    You might use a formula. With the text in A2, enter the following formula in B2:

     

    =LEFT(A2,3)&" "&MID(A2,4,2)&" "&MID(A2,6,5)&" "&RIGHT(A2,5)

     

    This can be filled down.

     

    Alternatively, you can use a macro to insert the spaces. This will replace the original values.

    Select the ranger with the values before running the macro.

     

    Sub InsertSpaces()
    Dim c As Range
    Application.ScreenUpdating = False
    For Each c In Selection
        If Len(c.Value) = 15 Then
            c.Value = Left(c.Value, 3) & " " & Mid(c.Value, 4, 2) & " " & _
                Mid(c.Value, 6, 5) & " " & Right(c.Value, 5)
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

Resources