Remove a "0"

S

Steved

Hello from Steved

I need to remove please the "0" I have a script below but do not know how to
adjust it to remove the "0"

from to this
7-086-1 7-86-1
7-094-1 7-94-1
7-099-1 7-99-1
7-024-1 7-24-1
7-024-1 7-24-1
7-084-1
7-081-1

Sub DelZeros2()
'Do Until ActiveCell = ""
For x = 1 To 1
Dim Rng As Range
Set Rng = Range("E1", Range("E56000").End(xlUp))
Range("E1").Select
For Each cell In Rng
If cell.Value <> "" Then
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"
ActiveCell.Offset(1, -1).Select
End If
Next cell
Next x
'Loop
End Sub

I thankyou.
 
C

cate

Hello from Steved

I need to remove please the "0" I have a script below but do not know howto
adjust it to remove the "0"

from        to this
7-086-1   7-86-1
7-094-1   7-94-1
7-099-1   7-99-1
7-024-1   7-24-1
7-024-1   7-24-1
7-084-1
7-081-1

Sub DelZeros2()
'Do Until ActiveCell = ""
For x = 1 To 1
    Dim Rng As Range
    Set Rng = Range("E1", Range("E56000").End(xlUp))
    Range("E1").Select
    For Each cell In Rng
    If cell.Value <> "" Then
        ActiveCell.Offset(0, 1).Select
        ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"
        ActiveCell.Offset(1, -1).Select
    End If
    Next cell
Next x
'Loop
End Sub

I thankyou.
Click to expand...

http://www.mvps.org/dmcritchie/excel/strings.htm Neat Site!
There's a replace function in there I think might help.
 
S

Steved

Hello Sergey

Firstly thankyou for taking timeout on my issue.

I shoulsd have explained a little futher I'm sorry I need to remove the
first "0" only please.

7-080-1 to this 7-80-1

Regards

Steved


Sergey Poberezovskiy said:
Steved,

Try something like the following:

For Each cell In Rng
cell.Value = Replace(cell.Value, "0", "")
Next

Steved said:
Hello from Steved

I need to remove please the "0" I have a script below but do not know how to
adjust it to remove the "0"

from to this
7-086-1 7-86-1
7-094-1 7-94-1
7-099-1 7-99-1
7-024-1 7-24-1
7-024-1 7-24-1
7-084-1
7-081-1

Sub DelZeros2()
'Do Until ActiveCell = ""
For x = 1 To 1
Dim Rng As Range
Set Rng = Range("E1", Range("E56000").End(xlUp))
Range("E1").Select
For Each cell In Rng
If cell.Value <> "" Then
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"
ActiveCell.Offset(1, -1).Select
End If
Next cell
Next x
'Loop
End Sub

I thankyou.
Click to expand...
Click to expand...
 
S

Sergey Poberezovskiy

Steved,

Try something like the following:

For Each cell In Rng
cell.Value = Replace(cell.Value, "0", "")
Next
 
S

Sergey Poberezovskiy

Then you can specify the optional parameter for Replace function:
Replace(cell.Value, "0", "", Count:=1)


Steved said:
Hello Sergey

Firstly thankyou for taking timeout on my issue.

I shoulsd have explained a little futher I'm sorry I need to remove the
first "0" only please.

7-080-1 to this 7-80-1

Regards

Steved


Sergey Poberezovskiy said:
Steved,

Try something like the following:

For Each cell In Rng
cell.Value = Replace(cell.Value, "0", "")
Next

Steved said:
Hello from Steved

I need to remove please the "0" I have a script below but do not know how to
adjust it to remove the "0"

from to this
7-086-1 7-86-1
7-094-1 7-94-1
7-099-1 7-99-1
7-024-1 7-24-1
7-024-1 7-24-1
7-084-1
7-081-1

Sub DelZeros2()
'Do Until ActiveCell = ""
For x = 1 To 1
Dim Rng As Range
Set Rng = Range("E1", Range("E56000").End(xlUp))
Range("E1").Select
For Each cell In Rng
If cell.Value <> "" Then
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"
ActiveCell.Offset(1, -1).Select
End If
Next cell
Next x
'Loop
End Sub

I thankyou.
Click to expand...
Click to expand...
Click to expand...
 
M

Mishell

Here is a way to do it :

Sub DelZeros2()
'Do Until ActiveCell = ""
For x = 1 To 1
Dim Rng As Range
Set Rng = Range("E1", Range("E56000").End(xlUp))
Range("E1").Select
For Each cell In Rng
If cell.Value <> "" Then
ActiveCell.Offset(0, 1).Select

a = cell.Value
b = InStr(a, "-0")
If b > 0 Then
a = Mid(a, 1, b) & Mid(a, b + 2)
ActiveCell.Value = a
End If

ActiveCell.Offset(1, -1).Select
End If
Next cell
Next x
'Loop
End Sub
 
S

Steved

Thankyou.

Sergey Poberezovskiy said:
Then you can specify the optional parameter for Replace function:
Replace(cell.Value, "0", "", Count:=1)


Steved said:
Hello Sergey

Firstly thankyou for taking timeout on my issue.

I shoulsd have explained a little futher I'm sorry I need to remove the
first "0" only please.

7-080-1 to this 7-80-1

Regards

Steved


Sergey Poberezovskiy said:
Steved,

Try something like the following:

For Each cell In Rng
cell.Value = Replace(cell.Value, "0", "")
Next

:

Hello from Steved

I need to remove please the "0" I have a script below but do not know how to
adjust it to remove the "0"

from to this
7-086-1 7-86-1
7-094-1 7-94-1
7-099-1 7-99-1
7-024-1 7-24-1
7-024-1 7-24-1
7-084-1
7-081-1

Sub DelZeros2()
'Do Until ActiveCell = ""
For x = 1 To 1
Dim Rng As Range
Set Rng = Range("E1", Range("E56000").End(xlUp))
Range("E1").Select
For Each cell In Rng
If cell.Value <> "" Then
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"
ActiveCell.Offset(1, -1).Select
End If
Next cell
Next x
'Loop
End Sub

I thankyou.
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

I had to be more careful. Some of the data changed to dates after I ran some of
the macros.

I'd use:

Option Explicit
Sub testme01()
Dim myRng As Range
Dim myCell As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No constants in that selected area!"
Exit Sub
End If

myRng.NumberFormat = "@" 'text

For Each myCell In myRng.Cells
With myCell
.Value = Replace(.Value, "-0", "-")
End With
Next myCell

End Sub
Hello from Steved

I need to remove please the "0" I have a script below but do not know how to
adjust it to remove the "0"

from to this
7-086-1 7-86-1
7-094-1 7-94-1
7-099-1 7-99-1
7-024-1 7-24-1
7-024-1 7-24-1
7-084-1
7-081-1

Sub DelZeros2()
'Do Until ActiveCell = ""
For x = 1 To 1
Dim Rng As Range
Set Rng = Range("E1", Range("E56000").End(xlUp))
Range("E1").Select
For Each cell In Rng
If cell.Value <> "" Then
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"
ActiveCell.Offset(1, -1).Select
End If
Next cell
Next x
'Loop
End Sub

I thankyou.
Click to expand...
 
J

J_Knowles

If all the string are consistently formatted "7-0xx-1", give this a try. Put
the formula in col B and copy it down as far as needed.

Col A ColB
7-080-1 ="7-"&MID(A1,4,2)&"-1"


If you need to get back to the text string without the formlae, do the
following:
Say you have the formula in B1:B100, highlight the range press ctrl+c (copy)
inside the highlighted range right click, select paste special - Values


HTH
 
K

K_Macd

If the data is already in a spreadsheet how about using the substitute
function with "-0" becoming "-". Much easier than a macro unless you want to
automate a regularly repeating task
--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"


Steved said:
Thankyou.

Sergey Poberezovskiy said:
Then you can specify the optional parameter for Replace function:
Replace(cell.Value, "0", "", Count:=1)


Steved said:
Hello Sergey

Firstly thankyou for taking timeout on my issue.

I shoulsd have explained a little futher I'm sorry I need to remove the
first "0" only please.

7-080-1 to this 7-80-1

Regards

Steved


:

Steved,

Try something like the following:

For Each cell In Rng
cell.Value = Replace(cell.Value, "0", "")
Next

:

Hello from Steved

I need to remove please the "0" I have a script below but do not know how to
adjust it to remove the "0"

from to this
7-086-1 7-86-1
7-094-1 7-94-1
7-099-1 7-99-1
7-024-1 7-24-1
7-024-1 7-24-1
7-084-1
7-081-1

Sub DelZeros2()
'Do Until ActiveCell = ""
For x = 1 To 1
Dim Rng As Range
Set Rng = Range("E1", Range("E56000").End(xlUp))
Range("E1").Select
For Each cell In Rng
If cell.Value <> "" Then
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1+(LEFT(RC[-1])=""0""),99)"
ActiveCell.Offset(1, -1).Select
End If
Next cell
Next x
'Loop
End Sub

I thankyou.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

My attempt to do a Replace 9
Can Vba workout if a selection is part of a group? 0
Data samples 3
"=TEXT(RC[-1],"MMM")" 4
Configure macro to run on all sheets Excel 2k7 9
Help me4 2
simple little loop 3
Combining VBA code for columns 8

Top