How to remove all Zero"0" & space from left side

**nur2544** · 02-01-2013, 03:15 AM

How to delete all zero & space from left side of a particular data with the help of macro

i have data like below
000000000000 010080023000
i want as 10080023000

Again i have like this
000000000000 200000000008
i want like 200000000008

000000004000 000000000000 = 4000000000000000
00000000E000 000000000000 = E000000000000000

000000000020 000040000000 = 20000040000000

i have lots of data like above in the range (D:J).

**nested.if1@gmail.com** · 02-01-2013, 03:20 AM

Please upload sample data.

Also check if the below is helpful.....

Assuming data is in D1 enter formula =TEXT(SUBSTITUTE(D1," ",""),"0")

**jindon** · 02-01-2013, 03:32 AM

Select the range then run the code

Sub test()
    Dim r As Range
    With CreateObject("VBScript.RegExp")
        .Pattern = "^0+"
        For Each r In Selection
            r.NumberFormat = "@"
            r.Value = .Replace(Replace(r.Value, " ", ""), "")
        Next
    End With
End Sub

**zbor** · 02-01-2013, 03:42 AM

Here, try this:

Formula:

=SUBSTITUTE(MID(A1,FIND(LEFT(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),""),0,""),1),A1),255)," ","")

**:) Sixthsense :)** · 02-01-2013, 04:08 AM

Or

Sub RemoveZeorsAndSpaces()
Dim c As Range, nEndRw As Long

nEndRw = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False

For Each c In Range("A1:J" & nEndRw)
    If c.Value <> "" Then
        If Left(c.Value, 1) = 0 Then
            c.Value = WorksheetFunction.Substitute(c.Value, " ", "")
            c.NumberFormat = "@"
            Do While Left(c.Value, 1) = 0
                c.Value = Mid(c.Value, 2, Len(c.Value))
            Loop
        End If
    End If
Next c

Application.ScreenUpdating = True
End Sub