Format alphanumeric field in Excel

[streksh]

I'm trying to format a text column in Excel to include dashes, but
cannot get this to work for a text field. For example, if i have an
account number that includes numbers and letters, I would like anyone
using the spreadsheet to type the account number, but have the cell
automatically add the dashes (567HA-4B). I don't see any way to do
this if the field contains non-numeric characters. Can anyone help
please?

 

[Ron Rosenfeld]

I'm trying to format a text column in Excel to include dashes, but
cannot get this to work for a text field. For example, if i have an
account number that includes numbers and letters, I would like anyone
using the spreadsheet to type the account number, but have the cell
automatically add the dashes (567HA-4B). I don't see any way to do
this if the field contains non-numeric characters. Can anyone help
please?

Number formatting only applies to numeric entries.

To do what you want, you will need to use an event-triggered VBA macro.

You could use custom formats IF the only variables in the account number were
the digits, but that is probably not the case.

To enter this event-triggered Macro, right click on the sheet tab.
Select "View Code" from the right-click drop-down menu.
Then paste the code below into the window that opens.

Be sure to set r to the range where you want this to occur.

You may also need to alter the testing I've entered for valid entries.

===========================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Set r = Range("A:A")

If Not Intersect(r, Target) Is Nothing Then
For Each c In Target

'validate entry
'use value2 as date formats sometimes cause
' a problem
If InStr(c.Value2, "-") = 0 And _
Len(c.Value2) = 7 Then

c.Value = UCase(Format(c.Value2, "@@@@@-@@"))
End If
Next c
End If

End Sub
============================
--ron

 

[Alan]

You can use the Worksheet_SelectionChange event (i.e., when user
goes to another cell after entry) to trigger this, and use string
functions to parse the string.

Here is some quick, example code that seems to work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const LeftNumChars = 5
Dim EnteredString As String
EnteredString = ""
' Check that the user modified a single cell
If Target.Cells.Count = 1 Then
' Check that the cell is in Column of interest (e.g., "D" = 4)
If Target.Column = 4 Then
' Get the value of the changed cell and remove leading/
trailing blanks
EnteredString = Trim$(ActiveSheet.Cells(Target.Row - 1,
Target.Column).Value)
' Make sure there is not already a dash in the cell
If Not InStr(EnteredString, "-") Then
' Check the length of the entered string
If Len(EnteredString) > LeftNumChars Then
'Get the right and left pieces of the string, and
insert the dash between them
EnteredString = Left$(EnteredString, LeftNumChars)
& "-" & _
Right$(EnteredString, Len(EnteredString) -
LeftNumChars)
' Write this value to the cell
ActiveSheet.Cells(Target.Row - 1,
Target.Column).Value = EnteredString
End If
End If
End If
End If
End Sub

Make sure you put this in a module associated with the worksheet of
interest. In the VBE Project window, right click on the worksheet to
which you wish to apply this code and then click "View Code" to get
started. See http://www.dailydoseofexcel.com/archives/2004/05/03/beginning-vba-events/.

Also, see http://www.techonthenet.com/excel/formulas/index_vba.php if
you are not familiar with the string functions.

HTH, Alan

 

[Rick Rothstein]

If InStr(c.Value2, "-") = 0 And Len(c.Value2) = 7 Then

Some might find this interesting... here is a way to perform the above test
from your code without using any function calls...

If c.Value2 Like "[!-][!-][!-][!-][!-][!-][!-]" Then

 

[streksh]

   You can use the Worksheet_SelectionChange event (i.e., when user
goes to another cell after entry) to trigger this, and use string
functions to parse the string.

Here is some quick, example code that seems to work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const LeftNumChars = 5
    Dim EnteredString As String
    EnteredString = ""
    ' Check that the user modified a single cell
    If Target.Cells.Count = 1 Then
        ' Check that the cell is in Column of interest (e.g., "D"= 4)
        If Target.Column = 4 Then
            ' Get the value of the changed cell and remove leading/
trailing blanks
            EnteredString = Trim$(ActiveSheet.Cells(Target.Row - 1,
Target.Column).Value)
            ' Make sure there is not already a dash in the cell
            If Not InStr(EnteredString, "-") Then
                ' Check the length of the entered string
                If Len(EnteredString) > LeftNumChars Then
                    'Get the right and left pieces ofthe string, and
insert the dash between them
                    EnteredString = Left$(EnteredString, LeftNumChars)
& "-" & _
                            Right$(EnteredString, Len(EnteredString) -
LeftNumChars)
                    ' Write this value to the cell
                    ActiveSheet.Cells(Target.Row - 1,
Target.Column).Value = EnteredString
                End If
            End If
        End If
    End If
End Sub

Make sure you put this in a module associated with the worksheet of
interest.  In the VBE Project window, right click on the worksheet to
which you wish to apply this code and then click "View Code" to get
started.  Seehttp://www.dailydoseofexcel.com/archives/2004/05/03/beginning-vba-eve....

Also, seehttp://www.techonthenet.com/excel/formulas/index_vba.phpif
you are not familiar with the string functions.

HTH, Alan

Thanks to everyone for all of the help!! All of the replies were
extremely helpful. I did find a minor bug...if i scroll back up
through the cells in column D, it keeps adding the "-" over and over
and eventually errors out. Is there a way to reset the values or
inspect for an existing dash so that it doesn't do that? I'm not sure
how many users would actually do that, but I don't like to leave a
known issue....

 

[Rick Rothstein]

Did you try Ron's code? I think it will work as you expect.

--
Rick (MVP - Excel)

You can use the Worksheet_SelectionChange event (i.e., when user
goes to another cell after entry) to trigger this, and use string
functions to parse the string.

Here is some quick, example code that seems to work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const LeftNumChars = 5
Dim EnteredString As String
EnteredString = ""
' Check that the user modified a single cell
If Target.Cells.Count = 1 Then
' Check that the cell is in Column of interest (e.g., "D" = 4)
If Target.Column = 4 Then
' Get the value of the changed cell and remove leading/
trailing blanks
EnteredString = Trim$(ActiveSheet.Cells(Target.Row - 1,
Target.Column).Value)
' Make sure there is not already a dash in the cell
If Not InStr(EnteredString, "-") Then
' Check the length of the entered string
If Len(EnteredString) > LeftNumChars Then
'Get the right and left pieces of the string, and
insert the dash between them
EnteredString = Left$(EnteredString, LeftNumChars)
& "-" & _
Right$(EnteredString, Len(EnteredString) -
LeftNumChars)
' Write this value to the cell
ActiveSheet.Cells(Target.Row - 1,
Target.Column).Value = EnteredString
End If
End If
End If
End If
End Sub

Make sure you put this in a module associated with the worksheet of
interest. In the VBE Project window, right click on the worksheet to
which you wish to apply this code and then click "View Code" to get
started.
Seehttp://www.dailydoseofexcel.com/archives/2004/05/03/beginning-vba-eve....

Also, seehttp://www.techonthenet.com/excel/formulas/index_vba.phpif
you are not familiar with the string functions.

HTH, Alan

Thanks to everyone for all of the help!! All of the replies were
extremely helpful. I did find a minor bug...if i scroll back up
through the cells in column D, it keeps adding the "-" over and over
and eventually errors out. Is there a way to reset the values or
inspect for an existing dash so that it doesn't do that? I'm not sure
how many users would actually do that, but I don't like to leave a
known issue....

 

[Ron Rosenfeld]

Some might find this interesting... here is a way to perform the above test
from your code without using any function calls...

If c.Value2 Like "[!-][!-][!-][!-][!-][!-][!-]" Then

*I* find it interesting. Thanks.
--ron

 

[Ron Rosenfeld]

Thanks to everyone for all of the help!! All of the replies were
extremely helpful. I did find a minor bug...if i scroll back up
through the cells in column D, it keeps adding the "-" over and over
and eventually errors out. Is there a way to reset the values or
inspect for an existing dash so that it doesn't do that? I'm not sure
how many users would actually do that, but I don't like to leave a
known issue....

My code *should* do that. Did you try it?

If it is not working, please post back exactly what you mean by "scroll back up
through ..."
--ron

 

[Rick Rothstein]

' Make sure there is not already a dash in the cell

If Not InStr(EnteredString, "-") Then

The above line from your code will not work as you intend it to. InStr does
not return a Boolean value, so logical expression in your If statement will
**always** evaluate to True. The only value it could ever return False for
is if the InStr function evaluated to -1, but since this function always
returns either 0 or a positive number, the value produced by applying the
Not operator to it will always produce a value other than -1. I think the
statement you would want to use here is this...

If InStr(EnteredString, "-") = 0 Then

 

[Rick Rothstein]

If it is not working, please post back exactly what you mean by

"scroll back up through ..."

The problem is the OP is using Alan's code and that code has an error in one
of its lines of code that makes the code always execute (I just posted a
correction for Alan to consider); on top of which, Alan's code makes use of
the SelectionChange event... these two things taken together means Alan's
originally posted code will always insert dashes into the text every time
the user makes a cell in the range active, whether anything in the cell is
changed or not.

 

[Ron Rosenfeld]

The problem is the OP is using Alan's code and that code has an error in one
of its lines of code that makes the code always execute (I just posted a
correction for Alan to consider); on top of which, Alan's code makes use of
the SelectionChange event... these two things taken together means Alan's
originally posted code will always insert dashes into the text every time
the user makes a cell in the range active, whether anything in the cell is
changed or not.

OIC. Tks
--ron