How do I remove the apostrophe(') in front of zeros

T

Tommy

I have alot of data that has an apostrophe in front of the numbers with
leading zeros.

How can i remove the apostrophe and keep the zeros in front.

For example:

'00012345 --> 00012345
'00123456 --> 00123456
 
L

Learning VBA

First custom format with 00000000
Then find and replace
Find '0
Replace with 0
 
F

FSt1

hi
you don't. AND keep the leading zeros.
mathmaticly leading zeros are unnecessary so excel strips them off and the
"number" becomes a true number. now can do mathmatical things to it like add,
divide, ect.
"numbers" with leading zeros are usually identification nomenclatures such
as part "numbers" or phone "numbers" or SS "numbers" and you usually don't
want to do mathmatical things to them. have you ever had a reason to divide
your phone number by 2 or add 1 to your street address. these are not true
numbers. they are IDs.
i have seen many posts from individuls want to know how to add leading zeros
to some id number they have and the responce is always the same. Pre-format
the cell as text or preceed the entry with an apostrophe.
the apostrophe is only visible in the formula bar. is there any reason why
this is perticularly offensive to you???

regards
FSt1
 
M

muddan madhu

try this, select the cells, then run the macro

Sub clean_it()
Dim r As Range
For Each r In Selection
r.Value = r.Text
Next r
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top