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!)
GuglaniAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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)
NorieAnalyst Assistant Commented:
How are you removing the letters from the value in the cell?
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
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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

Thanks for the solutions till now.
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GuglaniAuthor Commented:
Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.