Hello,

I'm trying to custom format a column but nothing happens when I apply the
formatting. The column contains entries such as this:
N163975A
B142237K
B141917K
B144973K
B189601K
and I'm trying to format them so they look like this:
N16-3975-A
B14-2237-K
B14-1917-K
B14-4973-K
B18-9601-K

I've confirmed the tools/options/view and the formula box is unchecked. I
have also cleared all formatting before trying to apply my own and still
nothing.

TIA,
Marta

you can't custom format text like you can numbers.
try =left(A1,3)&"-"&Mid(A1,4,4)&"-"&right(A1,1)
copy and paste special

"Marta" wrote:

Hello,

I'm trying to custom format a column but nothing happens when I apply the
formatting. The column contains entries such as this:
N163975A
B142237K
B141917K
B144973K
B189601K
and I'm trying to format them so they look like this:
N16-3975-A
B14-2237-K
B14-1917-K
B14-4973-K
B18-9601-K

I've confirmed the tools/options/view and the formula box is unchecked. I
have also cleared all formatting before trying to apply my own and still
nothing.

TIA,
Marta

You cannot format text that way, you would need a help column and a formula

=LEFT(A1,3)&"-"&MID(A1,4,4)&"-"&RIGHT(A1,1)


--


Regards,


Peo Sjoblom


"Marta" wrote in message
...
Hello,

I'm trying to custom format a column but nothing happens when I apply the
formatting. The column contains entries such as this:
N163975A
B142237K
B141917K
B144973K
B189601K
and I'm trying to format them so they look like this:
N16-3975-A
B14-2237-K
B14-1917-K
B14-4973-K
B18-9601-K

I've confirmed the tools/options/view and the formula box is unchecked. I
have also cleared all formatting before trying to apply my own and still
nothing.

TIA,
Marta

If a macro solution is okay for you to use, then you might want to consider
this...

1) You already entered the numbers and want to change them. If this is the
case, you can select those already filled in cells and run this macro...

Public Sub InsertDashes()
Dim C As Range
For Each C In Selection
If C.Value Like "[A-Za-z]######[A-Za-z]" Then
C.Value = Format(C.Value, "@@@-@@@@-@")
End If
Next
End Sub


2) You want to type in the 8-character entry and have it change to the
format you want when you enter it. Add this Worksheet Change event to the
code window for the sheet where you will be entering your "numbers"
(right-click on that sheet's tab and select View Code from the popup
menu)...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Col As Range
Set Col = Range("A:A")
On Error Resume Next
If Not Intersect(Col, Target) Is Nothing Then
If Target.Value Like "[A-Za-z]######[A-Za-z]" Then
Application.EnableEvents = False
Target.Value = Format(Target.Value, "@@@-@@@@-@")
Application.EnableEvents = True
End If
End If
End Sub

By the way, I have assumed your 8-character entry is always a letter
followed by 6 digits followed by a letter. If that is not the case, then let
us know what your parameters are and someone here will modify the code for
you.

Rick


"Marta" wrote in message
...
Hello,

I'm trying to custom format a column but nothing happens when I apply the
formatting. The column contains entries such as this:
N163975A
B142237K
B141917K
B144973K
B189601K
and I'm trying to format them so they look like this:
N16-3975-A
B14-2237-K
B14-1917-K
B14-4973-K
B18-9601-K

I've confirmed the tools/options/view and the formula box is unchecked. I
have also cleared all formatting before trying to apply my own and still
nothing.

TIA,
Marta