How i can remove 000 from 0004317575303272489

D

dalipsinghbisht

Hi,


I have one problem in Excel what formula i should apply to remov
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....

Thank
 
G

Guest

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"
 
J

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"
 
D

dalipsinghbisht

Hi Friends,

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


Dali
 
G

Guest

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"
 
R

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"
message
Hi Friends,

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


Dalip
 
Top