Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

Issues of reading zero in excel

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
Guglani
Asked:
Guglani
  • 3
  • 2
  • 2
  • +1
1 Solution
 
thehagmanCommented:
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
 
NorieVBA ExpertCommented:
How are you removing the letters from the value in the cell?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
GuglaniAuthor Commented:
@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
 
NorieVBA ExpertCommented:
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
 
GuglaniAuthor Commented:
I am logging off for the day! Will reply to the responses tomorrow.

Thanks for the solutions till now.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
GuglaniAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now