Need assistance deleting nunbers in cells.

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
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:

Code:
[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]
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
I'm sure someone in here can answer this question. Please H E L P !:p
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,147
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
hi Mike!

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

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

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Try

Code:
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Oops :oops:

Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,147
Office Version
2013
Platform
Windows

ADVERTISEMENT

Hi Peter
I thought Pedie wanted to delete the contents if greater than 4 numbers, and keep anything else
Code:
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

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Thanks, thanks alot Vog & Mike & everyoen who helped....that was what I am looking for...
I am studying different ways a macro can work...:biggrin: it has help me lot in different ways.
Pedie
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,147
Office Version
2013
Platform
Windows
Pleasure Pedie
Glad you got it sorted.
Keep working on it and it will get better.

But it is frustrating at times.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,855
Messages
5,513,805
Members
408,971
Latest member
kay_dee8

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top