Solved

Issues of reading zero in excel

Posted on 2012-03-30
8
214 Views
Last Modified: 2012-04-04
Hi Experts,

I have data sheet which contains alphabets and numbers in every cell. I want to delete all the alphabets from the cell and just want numbers.

My difficulty is, when I try to remove the alphabets; the zero vanishes!

Please refer the example below:

ABC0123456 (Original Data in the software)

When I try to remove 'ABC' from the cell,

It shows 123456 (The 0 is not there!)
0
Comment
Question by:Guglani
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 20

Expert Comment

by:thehagman
ID: 37786871
Try entering
'0123456

Open in new window

The apostrophe tells the system that the thig you enter is to be treated as text even if it looks like a number (you will also note that by default it is left-aligned like text instead of right alighned like numbers, also that big numbers are not rewritten as exponentials and the like)
0
 
LVL 33

Expert Comment

by:Norie
ID: 37786873
How are you removing the letters from the value in the cell?
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37786891
Hello,

how exactly do you go about removing "ABC" from the cell? If you just delete the alpha characters, Excel will see the remaining numbers as ... well, numbers, and numbers are by default displayed without leading zeros.

You could

- replace ABC with the ' character (that is the single quotation mark)

- use a formula in another cell to display just the numbers, like
=TEXT(MID(A1,4,7),"0000000")

- remove the ABC and then format the result with a custom format of
0000000

As a general rule, numbers will not show leading zeros by default, You can use a custom format to display leading zeros or you can convert the number into text with leading zeros, as shown above. Just be aware that once transformed into text, these values will be text and if you want to use them in formulas, you need to convert them into numeric values again.

cheers, teylyn
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Guglani
ID: 37786975
@teylyn

My requirement is to have zero(s) in the cell where they are mentioned. They should be applicable to the cell which contains zero not all cell.

Your formula tries to append a zero in all of them.

I tried replacing (Ctrl+H) by <space> but the zero vanishes too!

@imnorie
I trying to replace with Find and replace function (Ctrl+H) by  giving a <space>
0
 
LVL 33

Expert Comment

by:Norie
ID: 37786996
If you want to keep the leading 0's you'll either have to format the cell to show them, or instead of replacing with space, replace with '.
0
 

Author Comment

by:Guglani
ID: 37787049
I am logging off for the day! Will reply to the responses tomorrow.

Thanks for the solutions till now.
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 37789844
This formula will remove the first three characters from A1 and keep the leading zero intact. The result is text, not a number

=""&REPLACE(A1,1,3,"")

cheers, teylyn
0
 

Author Closing Comment

by:Guglani
ID: 37805441
Thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question