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]
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

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
21,274
Office Version
  1. 365
  2. 2007
Platform
  1. 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
21,274
Office Version
  1. 365
  2. 2007
Platform
  1. 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
21,274
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Pleasure Pedie
Glad you got it sorted.
Keep working on it and it will get better.

But it is frustrating at times.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,613
Messages
5,832,702
Members
430,155
Latest member
spacedad41

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top