Remove a "0"

[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.

 

[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.

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

 

[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

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.

 

[Sergey Poberezovskiy]

Steved,

Try something like the following:

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

 

[Sergey Poberezovskiy]

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

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.

 

[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

 

[Steved]

Thankyou.

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

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.

 

[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.

 

[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_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"

Thankyou.

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

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.