Luking for a formula to remove leading zeros in a text

[faizumarali]

Text as follows

Cell A1 = 0330000000693123456101400
Cell A2 = 0003300000006931234561014

Result should be in

B1 = 330000000693123456101400
B2 = 3300000006931234561014


Thank you





<table style="width: 186px; height: 20px;" border="0" cellpadding="0" cellspacing="0"><col width="186"><tr height="20"> <td style="height: 15pt; width: 140pt;" width="186" height="20">
</td> </tr></table>

 

[MrKowz]

Try the following - however keep in mind that Excel can only have accuracy to 15 places.

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Whole Number</TD><TD>Without leading 0s</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>0330000000693123456101400</TD><TD>330000000693123000000000</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>0003300000006931234561014</TD><TD>3300000006931230000000</TD></TR></TBODY></TABLE><TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B2</TH><TD style="TEXT-ALIGN: left">=VALUE(A2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[faizumarali]

That formula i have tried before but i get 3.3E+23 any other options. Thanks

 

[MrKowz]

Try the following UDF. To use this, press ALT+F11 to open the VBA editor, and go to Insert>Module. Copy/paste this code into the module and then follow the example.

Public Function TrimLeadZero(rng As Range) As String
Dim i   As Long
For i = 1 To Len(rng.Value)
    If Mid(rng.Value, i, 1) <> "0" Then
        TrimLeadZero = Mid$(rng.Value, i, Len(rng.Value) - i + 1)
        Exit Function
    End If
Next i
End Function

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Whole Number</td><td style=";">Without leading 0s</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">0330000000693123456101400</td><td style=";">330000000693123456101400</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">0003300000006931234561014</td><td style=";">3300000006931234561014</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=trimleadzero(<font color="Blue">A2</font>)</td></tr></tbody></table></td></tr></table><br />

 

[faizumarali]

Simply awesome ! Cheers. Thank you so much

 

[T. Valko]

Text as follows

Cell A1 = 0330000000693123456101400
Cell A2 = 0003300000006931234561014

Result should be in

B1 = 330000000693123456101400
B2 = 3300000006931234561014


Thank you

Try this...

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

Note that these will result in TEXT strings since Excel can only handle up to 15 digit numbers.