Remove a "0"
 

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.

Remove a "0"
 

On Dec 9, 4:32*pm, Steved wrote:
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.

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

Remove a "0"
 

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" wrote:

Steved,

Try something like the following:

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

"Steved" wrote:

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.

Remove a "0"
 

Steved,

Try something like the following:

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

"Steved" wrote:

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.

Remove a "0"
 

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


"Steved" wrote:

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" wrote:

Steved,

Try something like the following:

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

"Steved" wrote:

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.

Remove a "0"
 

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" a écrit dans le message de news:
...
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.

Remove a "0"
 

Thankyou.

"Sergey Poberezovskiy" wrote:

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


"Steved" wrote:

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" wrote:

Steved,

Try something like the following:

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

"Steved" wrote:

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.

Remove a "0"
 

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

Steved wrote:

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

Remove a "0"
 

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
--
Data Hog


"Steved" wrote:

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.

Remove a "0"
 

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" wrote:

Thankyou.

"Sergey Poberezovskiy" wrote:

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


"Steved" wrote:

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" wrote:

Steved,

Try something like the following:

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

"Steved" wrote:

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.