Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=SUBSTITUTE(C4,"~?#","") will this work to remove multiple string | Excel Worksheet Functions | |||
using VBA remove "0" but leave "X" first digit only please. | Excel Programming | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |