Insert a new worksheet named "result" and run this macro! The macro supposes
your data being in range("A1:An") and this is the active sheet:

Sub test()
Dim currnum As Range
Set currnum = Range("A1")
rescount = 0
Do While Not IsEmpty(currnum)
rescount = rescount + 1
If Len(currnum) < 5 Or Len(currnum) 6 Then
MsgBox currnum.Value, vbOKOnly, "Invalid length!"
Else
If Len(currnum) = 6 And currnum.Value + 9 = Range("A" &
currnum.Row + 9) Then
Worksheets("result").Range("A" & rescount).Value =
currnum.Value / 10
Set currnum = Range("A" & currnum.Row + 10)
Else
Worksheets("result").Range("A" & rescount).Value =
currnum.Value
Set currnum = Range("A" & currnum.Row + 1)
End If
End If
Loop
End Sub

Regards,
Stefi

€žChris Mitchell€� ezt Ã*rta:

I have a list of numbers, some are 5 digits the others are 6 digits long.

For some of the 6 digit numbers there is a complete set, i.e. XXXXX0 through
to XXXXX9. Where this is the case I want to replace the existing 10 entries
with a single 5 digit number i.e. XXXXX. Where there is an incomplete set
of 6 digit numbers, then I want to leave the existing 6 digit numbers in the
list.

I also want all existing 5 digit numbers to be included in the finished
list.

How can I get excel to do this?

TIA.

Chris.