Hi,


I have one problem in Excel what formula i should apply to remove
Zero's from a number

i.e. i want to remove 000 from

0004317575303272489
0004317575015221980
0004006661030893619

I want the above data as

4317575303272489
4317575015221980
4006661030893619
So Please Tell the solution As soon as possible....

Thanks


--
dalipsinghbisht
------------------------------------------------------------------------
dalipsinghbisht's Profile: http://www.excelforum.com/member.php...o&userid=32557
View this thread: http://www.excelforum.com/showthread...hreadid=523514

Hi

You could use Find/Replace - if you are sure that there is only one
occurence of 000 in any particular number. If you had 0004317570003272489,
for example, that method would remove both sets of 000.
If it is always the first three digits, you could use a helper column with
=MID(A2,4,255)

Hope this helps.
Andy.

"dalipsinghbisht"
<dalipsinghbisht.24tasm_1142595601.3047@excelfor um-nospam.com wrote in
message news:dalipsinghbisht.24tasm_1142595601.3047@excelf orum-nospam.com...

Hi,


I have one problem in Excel what formula i should apply to remove
Zero's from a number

i.e. i want to remove 000 from

0004317575303272489
0004317575015221980
0004006661030893619

I want the above data as

4317575303272489
4317575015221980
4006661030893619
So Please Tell the solution As soon as possible....

Thanks


--
dalipsinghbisht
------------------------------------------------------------------------
dalipsinghbisht's Profile:
http://www.excelforum.com/member.php...o&userid=32557
View this thread: http://www.excelforum.com/showthread...hreadid=523514

another approach, in a blank column next enter =A1 + 0
leading 000's should fall off and your content should be a pure number
(copy and paste-special value to itself)

"dalipsinghbisht"
<dalipsinghbisht.24tasm_1142595601.3047@excelfor um-nospam.com wrote in
message news:dalipsinghbisht.24tasm_1142595601.3047@excelf orum-nospam.com...

Hi,


I have one problem in Excel what formula i should apply to remove
Zero's from a number

i.e. i want to remove 000 from

0004317575303272489
0004317575015221980
0004006661030893619

I want the above data as

4317575303272489
4317575015221980
4006661030893619
So Please Tell the solution As soon as possible....

Thanks


--
dalipsinghbisht
------------------------------------------------------------------------
dalipsinghbisht's Profile:
http://www.excelforum.com/member.php...o&userid=32557
View this thread: http://www.excelforum.com/showthread...hreadid=523514

Hi Friends,

Thanks for your quick reply
Andy's second formula is working but zim's not coz it shows zero in
last......


Dalip


--
dalipsinghbisht
------------------------------------------------------------------------
dalipsinghbisht's Profile: http://www.excelforum.com/member.php...o&userid=32557
View this thread: http://www.excelforum.com/showthread...hreadid=523514

Hi

JIm's formula should be OK. It is attempting to make the 'number' into a
proper number, by telling Excel to add 0 to it - and it usually does the
trick! Make sure you are using + and not &
It may be that there are trailing blanks in the cell, I suppose.
You could also try *1 instead of +0

Andy.

"dalipsinghbisht"
<dalipsinghbisht.24td3y_1142598600.792@excelforu m-nospam.com wrote in
message news:dalipsinghbisht.24td3y_1142598600.792@excelfo rum-nospam.com...

Hi Friends,

Thanks for your quick reply
Andy's second formula is working but zim's not coz it shows zero in
last......


Dalip


--
dalipsinghbisht
------------------------------------------------------------------------
dalipsinghbisht's Profile:
http://www.excelforum.com/member.php...o&userid=32557
View this thread: http://www.excelforum.com/showthread...hreadid=523514

You can use TTC (Text To Columns).

Select your column of data, then,
<Data <TextToColumns
Click on "Fixed Width", then <Next

Click in the "Data Preview" window and place the 'break line' to separate
the zeroes,
Then <Next

In the "Data Preview" window you see the first column of 0's selected by
default.
Click on "Do Not Import Column"
You see the header change from 'General' to "skip".

Then click on the second column containing your numbers.
Click on "Text", since you have 16 digits remaining.
Then <Finish.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"dalipsinghbisht"
<dalipsinghbisht.24td3y_1142598600.792@excelforu m-nospam.com wrote in
message news:dalipsinghbisht.24td3y_1142598600.792@excelfo rum-nospam.com...

Hi Friends,

Thanks for your quick reply
Andy's second formula is working but zim's not coz it shows zero in
last......


Dalip


--
dalipsinghbisht
------------------------------------------------------------------------
dalipsinghbisht's Profile:
http://www.excelforum.com/member.php...o&userid=32557
View this thread: http://www.excelforum.com/showthread...hreadid=523514