Solved

Issues of reading zero in excel

Posted on 2012-03-30
8
210 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:teylyn
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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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:
teylyn 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
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…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now