How to remove consecutive number string only from a column ?

[sezuh]

Hi,
I have a list of number string and I would like to remove the one with consecutive numbers onlyand paste it to next column.I don’t mind if its excel formula or UDF.Any suggestion or help would be much appreciated in advance.
Here is an example.

[TABLE="width: 316"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]LIST[/TD]
[TD="colspan: 2"]EXPECTED RESULT[/TD]
[/TR]
[TR]
[TD]16 27 33 37[/TD]
[TD]16 27 33 37[/TD]
[TD]18 19 20 21[/TD]
[/TR]
[TR]
[TD]18 19 20 21[/TD]
[TD]01 19 20 32[/TD]
[TD]36 37 38 39[/TD]
[/TR]
[TR]
[TD]01 19 20 32[/TD]
[TD]08 26 32 39[/TD]
[TD]01 02 03 04[/TD]
[/TR]
[TR]
[TD]08 26 32 39[/TD]
[TD]13 14 24 25[/TD]
[TD]34 35 36 37[/TD]
[/TR]
[TR]
[TD]36 37 38 39[/TD]
[TD]03 04 30 31[/TD]
[TD]18 19 20 21[/TD]
[/TR]
[TR]
[TD]13 14 24 25[/TD]
[TD]05 20 30 32[/TD]
[TD]33 34 35 36[/TD]
[/TR]
[TR]
[TD]03 04 30 31[/TD]
[TD]06 25 36 38[/TD]
[TD]10 11 12 13[/TD]
[/TR]
[TR]
[TD]01 02 03 04[/TD]
[TD]12 13 28 31[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]34 35 36 37[/TD]
[TD]01 04 17 19[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]05 20 30 32[/TD]
[TD]09 31 36 38[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18 19 20 21[/TD]
[TD]27 28 33 34[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]06 25 36 38[/TD]
[TD]08 09 19 33[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12 13 28 31[/TD]
[TD]02 06 24 33[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]01 04 17 19[/TD]
[TD]13 30 33 35[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]09 31 36 38[/TD]
[TD]19 21 27 35[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]33 34 35 36[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]27 28 33 34[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]08 09 19 33[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10 11 12 13[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]02 06 24 33[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13 30 33 35[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]19 21 27 35[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Thanks again

 

[JLGWhiz]

Give this a shot:

Sub mit()
Dim sh As Worksheet, lr As Long, rng As Range, c As Range, rw As Variant
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A1:A" & lr)
    For Each c In rng
        rw = Split(c.Value, " ")
        For i = LBound(rw) To UBound(rw) - 1
            If CLng(rw(i)) + 1 <> CLng(rw(i + 1)) Then
                Exit For
            ElseIf i = 2 Then
                c.Copy c.Offset(lr, 1).End(xlUp)(2)
            End If
        Next
    Next
End Sub

 

[sezuh]

Hi JLGWHIZ,
Thanks very much for your help,the code works perfect,much appreciated.
Much obliged.

 

[JLGWhiz]

Hi JLGWHIZ,
Thanks very much for your help,the code works perfect,much appreciated.
Much obliged.

You are welcome,
Regards, JLG

 

[Lukhanyo]

Give this a shot:

Sub mit()
Dim sh As Worksheet, lr As Long, rng As Range, c As Range, rw As Variant
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A1:A" & lr)
    For Each c In rng
        rw = Split(c.Value, " ")
        For i = LBound(rw) To UBound(rw) - 1
            If CLng(rw(i)) + 1 <> CLng(rw(i + 1)) Then
                Exit For
            ElseIf i = 2 Then
                c.Copy c.Offset(lr, 1).End(xlUp)(2)
            End If

Can someone please explain to me the different parts of this VBA code and the basic meaning of the commands?

 

[Lukhanyo]

I have applied this VBA code, and my results still have consecutive numbers such as 12345, I would like to understand the code line by line, to see where I can modify it to suit my case.

Thanks in advance for explaining it to me line by line.

Thank you.

 

[dangelor]

Sub mit()
Dim sh As Worksheet, lr As Long, rng As Range, c As Range, rw As Variant, i As Long 'DECLARATION OF VARIABLES
Set sh = Sheets(1) 'Edit sheet name 'ASSIGNING Sheets(1) TO A VARIABLE
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row 'ASSIGNING THE NUMBER OF LAST USED ROW IN COLUMN 1 TO A VARIABLE
Set rng = sh.Range("A1:A" & lr) 'ASSIGNING ALL THE CELLS IN COLUMN A TO A RANGE
    For Each c In rng 'LOOPING THROUGH EACH CELL IN THE RANGE
        rw = Split(c.Value, " ") 'SPLITTING THE VALUE OF THE CURRENT CELL AND ASSIGNING THEM TO AN ARRAY
        For i = LBound(rw) To UBound(rw) - 1 'LOOPING THROUGH EACH ITEM IN THE ARRAY
            If CLng(rw(i)) + 1 <> CLng(rw(i + 1)) Then ' CHECKING IF THE CURRENT ITEM ISN'T 1 LESS THEN THE NEXT ITEM
                Exit For 'IF IT'S NOT CONSECUTIVE GO ON TO THE NEXT ITEM
            ElseIf i = 2 Then 'IF IT'S CONSECUTIVE AND i = 2 THEN
                c.Copy c.Offset(lr, 1).End(xlUp)(2) 'PLACE A COPY OF C INTO THE 2ND COLUMN
            End If
        Next
    Next
End Sub

 

[Lukhanyo]

/Users/lsifo2/Desktop/Screen Shot 2019-08-19 at 8.28.21 PM.png

 

[Lukhanyo]

Thank you very much for explaining this to me, I really appreciate it.

However, sadly the pattern has not changes, its still giving me consecutive numbers.

 

[Rick Rothstein]

and my results still have consecutive numbers such as 12345

You have 12345 in your data somewhere? How does that fit in with the data you showed us in Message #1 ?