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.

Sort the numbers in ascending order. Assuming you have a header row so
that the numbers start in A2, put this in B2:

=LEFT(A2,5)

and copy down, then put this in C2:

=IF(LEFT(A2,5)=LEFT(A1,5),"",COUNTIF(B:B,B2))

and copy down. This should give you the totals for each leading 5-
digits, so you can scan through looking for 10 (or 11, as you might
also have just the 5-digit part) and then manually replace the block
of 10 with a single row.

You can delete columns B and C when you've finished.

Hope this helps.

Pete

On Sep 29, 6:28*am, "Chris Mitchell"
wrote:
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.

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.

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.

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.

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.

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.