Solved

Issues of reading zero in excel

Posted on 2012-03-30
8
212 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
ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 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