How i can remove 000 from 0004317575303272489

[dalipsinghbisht]

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

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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Jim May]

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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[dalipsinghbisht]

Hi Friends,

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


Dalip

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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[RagDyeR]

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"
<[email protected]> wrote in
message news:[email protected]...

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