You are welcome! Thanks for the feedback!
Stefi

€žChris Mitchell€� ezt Ã*rta:

Thanks Stefi.

Worked OK this time after I tweaked it to allow for some unexpected 7 digit
numbers.

Thanks again.
"Stefi" wrote in message
...
Sorry, forgot to mention that you should place the macro in a normal
module
(not a sheet module):
Create sheet named "result"
make sure that your data sheet is the active sheet
Open VBE (Alt+F11)
right click on your workbook name
choose InsertModule from the local menu
Paste code in the Module code window
Place cursor between Sub and End Sub lines
RunRun Sub (F5)

Stefi


"Chris Mitchell" ezt Ã*rta:

Thanks Stefi.

Did as you suggested but all it did was to lock PC, had to Ctrl + alt +
delete to end excel.

Macros are another thing that I'm not familiar with so I probably did
something wrong.

I added a new sheet result, and created the Macro in the sheet that
contains
the data by copying and pasting your text and tidied up to get rid of red
warning text in VB, i.e. take out line wraps until text was all normal
(black). Then ran the macro, but no joy.

"Stefi" wrote in message
...
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.