Format text from aaabbb to aaa-bbb

[Stinky Pete]

Hi everyone,

I'm new to programming in Excel, so please be patient ;-)

I'm using an Excel 2000 (corporate policy) worksheet into which our
plant operators currently manually enter a unique batch number in the
text format of aaa-bbb. In reality, if you break the number down,
it's (alpha numeric)(alpha numeric)(numeric)-(numeric)(numeric)
(numeric) and for example it could be 9a5-123 or adr-456 etc.

Anyway, I wish to remove the possibility of the operators not
including the hyphen which could spell havoc when the product ends up
in customer land as it MUST be completely traceable.

So, any suggestions on how to allow the operators enter a text sting
as aaabbb and then get excel to format the text as aaa-bbb ??? I've
had a look at the group and nothing quite seems to fit. Thanx to the
group though, I did found some cool code that helps change any text to
uppercase.

Stinky Pete ;-)

 

[Joerg Mochikun]

You could use data validation. Here is an example for cell A1: Go to Data =>
Validation. Allow:Custom, Formula:
=AND(LEN(A1)=7,MID(A4,4,1)="-")

Above formula checks, if input in A1 consists of exactly 7 characters and if
the 4th character is a hyphen. It doesn't "correct" user input, but doesn't
allow incorrect input. You should provide Alert and Input messages to give
your users feedback on the correct data format.

Caveat: Data validation does not check values that are copied to the cell.
Only direct keyboard entries are checked.
Cheers,

Joerg

 

[Joerg Mochikun]

Here is a macro based solution.Open the VBA editor (Alt-F11) and put
following code into the code section of the worksheet that you want to check
.. The example code assumes that your entry cells are in A1:A10.
If a user inputs a value into a cell of this range, the macro puts a hyphen
into any entry that is exactly 6 characters long.

If the entry is 7 characters long, but does not contain a hyphen at the 4th
digit or if the entry is not blank, an error message appears and the entry
is deleted.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
If Len(Target.Value) = 6 Then
Target.Value = Left(Target.Value, 3) & "-" & Right(Target.Value,
3)
ElseIf Len(Target.Value) <> 7 And Mid(Target.Value, 4, 1) <> "-" And
Target.Value <> "" Then
MsgBox "Entries must be in format xxx-yyy!"
Target.Value = ""
End If
End If
End Sub

 

[Joerg Mochikun]

Version 2 of the macro: Corrects an error in the Elseif statement and gives
users a chance to correct the input:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
If Len(Target.Value) = 6 Then
Target.Value = Left(Target.Value, 3) & "-" & Right(Target.Value,
3)
ElseIf (Len(Target.Value) <> 7 Or Mid(Target.Value, 4, 1) <> "-")
And Target.Value <> "" Then
answer = InputBox("Please correct your input. Must be in format
xxx-yyy!", , Target.Value)
Target.Value = answer
End If
End If
End Sub

Cheers,
Joerg Mochikun

 

[Shane Devenshire]

Hi,

Select the range where you want to control the user entry and select Data,
Validation, choose List from the Allows box, choose Custom and enter the
following

=ISNUMBER(FIND("-",A1))

Where A1 is the first cell in the range you selected.

 

[dranon]

Hi,

Select the range where you want to control the user entry and select Data,
Validation, choose List from the Allows box, choose Custom and enter the
following

=ISNUMBER(FIND("-",A1))

Where A1 is the first cell in the range you selected.

Useless.

 

[Gord Dibben]

Not useless.

It won't format the data but it will prevent entry without the hyphen.


Gord Dibben MS Excel MVP

 

[Stinky Pete]

Not useless.

It won't format the data but it will prevent entry without the hyphen.

Gord Dibben  MS Excel MVP




- Show quoted text -

Wow. Some marvelous suggestions. Thanx everyone.

Stinky Pete ;-)