Need assistance deleting nunbers in cells.

[pedie]

I need a code to delete number in excel sheet if there are more then any four numbers & more together....eg 456332, 45743, 457342342000034 etc
but not number with less then 4 digits together.

I have the code for deleting all the number erespective but now i need something better so please help.

Thanks alot!
Pedie!

my current code below:

[FONT=Bell MT]Sub Pedie_dots()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT]
[FONT=Bell MT]Dim LR As Long, i As Long, j As Integer, c As Range<o:p></o:p>[/FONT]
[FONT=Bell MT]LR = Range("A" & Rows.Count).End(xlUp).Row<o:p></o:p>[/FONT]
[FONT=Bell MT]For Each c In Range("A1:D" & LR)<o:p></o:p>[/FONT]
[FONT=Bell MT]   For j = 0 To 9<o:p></o:p>[/FONT]
[FONT=Bell MT]       c.Value = Replace(c.Value, j, ".")<o:p></o:p>[/FONT]
[FONT=Bell MT]   Next j<o:p></o:p>[/FONT]
[FONT=Bell MT]Next c<o:p></o:p>[/FONT]
[FONT=Bell MT]End Sub<o:p></o:p>[/FONT]

 

[pedie]

I'm sure someone in here can answer this question. Please H E L P !

 

[Michael M]

Hi Pedie
What are you trying to achieve
Your code simply replaces each cell with a zero followed by a dot for each number that was in the cell
Do you want to delete all numbers with more than 4 digits and keep anything with 4 or less ??

Secondly, a tip for your benefit.....
by reposting on your own thread, you make it appear that someone has answered your question and thereby reducing the chance of an answer.
Be patient, others will get to you.

 

[pedie]

hi Mike!

be patient, 'm ok to that part...was just curious for an answer....

Okay, bout ur inquiry that is exactly wat i was looking for.
if any numbers together is more then 4, then delete that particular numbers together....and not numbers below it that is 4.

 

[VoG]

Try

Sub Pedie_dots()
Dim LR As Long, i As Long, j As Integer, c As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
For Each c In Range("A1:D" & LR)
   For j = 0 To 9
       If Len(c.Value) > 4 Then c.Value = Replace(c.Value, j, ".")
   Next j
Next c
End Sub

 

[VoG]

Oops

Sub Pedie_dots()
Dim LR As Long, i As Long, j As Integer, c As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
For Each c In Range("A1:D" & LR)
    If Len(c.Value) > 4 Then
        For j = 0 To 9
            c.Value = Replace(c.Value, j, ".")
        Next j
    If Len(c.Value) > 4 Then
Next c
End Sub

 

[Michael M]

Hi Peter
I thought Pedie wanted to delete the contents if greater than 4 numbers, and keep anything else

Sub Pedie_dots()
Dim LR As Long, i As Long, j As Integer, c As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
For Each c In Range("A1:D" & LR)
   If Len(c) > 4 Then
   c.Value = ""
   Else: c.Value = Left(c, 4)
   End If
   Next c
End Sub

 

[pedie]

Thanks, thanks alot Vog & Mike & everyoen who helped....that was what I am looking for...
I am studying different ways a macro can work... it has help me lot in different ways.
Pedie

 

[pedie]

Hi Peter
I thought Pedie wanted to delete the contents if greater than 4 numbers, and keep anything else

Mike that was what I was exactly thinking to do...great answer...I know my english in not up to the mark but i am doing my best in whichever ways i can do things better..

Thanks MIke:

 

[Michael M]

Pleasure Pedie
Glad you got it sorted.
Keep working on it and it will get better.

But it is frustrating at times.