Remove Leading zeros keeping hidden apostrophe as is

Hi Guys,

I have a column which contains some IDs. Now, most of these IDs are numeric barring few which are text. However, the formatting of all the cells is same which is General. I also see a hidden apostrophe at the begining of text or number in each of the cells of this column.

I need to remove the leading zeros from the cells which contain number (there would always be 4 leading zeros which I need to remove) and leave aside the cells which have text. Also, the leading apostrophe (which is hidden in all the cells) should remain in tact.

Ex -

'0000123456
'0000238983
'Text
'Free Text

What I want is

'123456
'238983
'Text
'Free Text

Please note that leading apostrophe appears only when I double click on the cell, else it is hidden.

Any help please.

Assuming those are in column A starting in A1, put this formula in B1:

=IF(ISNUMBER(A1*1),TEXT(A1*1,"0"),A1)

then copy down. Then you can fix the values in column B and delete column A.

Hope this helps.

Pete

I think unless you use find and replace (search for 0000 and replace with "") any formula based approach would get rid of the hidden '

The problem with that approach is it would remove 0000 strings from any position.

You could use a formula like = TEXT(--A1,"0") which would give you the number without leading 0's as a text string which is kind of the same thing, or you could use a vba solution like

Please Login or Register  to view this content.

which would maintain your apostrophe.

Yes, with both the above solutions, it removes the zeros but doesn't keep the ' either. Not working.

Use the vba option then.

Any other solution that will keep the apostrophe as well. If you are suggesting a macro, please help me how to enable that macro.

Regards,
Ashish

Hi Mubashir,

I can't do that as it is a 10 digit no. and can have '0000' somewhere in middle as well. Something like - '0000100001

Regards,
Ashish

As suggested in posts 3 and 7 you could try using find and replace - this would maintain the apostrophe.

If you need help implementing the macro please upload a workbook with an example of the layout of the data and I wil adjust the macro for you.

ragulduy,

Can you please let me know how can I apply that VBA to entire column. Where do I do that please?

Regards,
Ashish

Hi - please upload the workbook showing an example of the data and I will complete the macro.

Hi Ragulduy,

Really appreciate your help, but I would like to do it myself as there are several such workbooks where I need to do this.


Regards,
Ashish

Meanwhile,

I have attached a sample sheet. in this case, need to remove 7 leading zeros.

Regards,
Ashish

Hi, press alt+f11 to open the vba editor, select insert->new module and then post this code into the new module:

Please Login or Register  to view this content.

Then place the cursor between sub macro_1() and end sub and press F5 to run, whilst the sheet you want to change is active.

I'm not sure why you are hung up about the leading apostrophe. That is a formatting character, and forms no part of the value that is in the cell. You can see this if you do:

=LEFT(A2,1)

which returns "0", or:

=CODE(A2)

which returns 48 (the ascii code for "0"). You can enter a leading apostrophe when you type an entry into a cell, to force the entry to be treated as a text value, so leading zeros can be retained, but this is a throwback to the old Lotus days. I think the Lotus Compatibility settings in Excel Options can give rise to the leading apostrophe when using Paste Special | Values (in older versions of Excel, at least).

If you want to see the leading apostrophe explicitly, you can change my formula to this in B2:

="'"&IF(ISNUMBER(A2*1),TEXT(A2*1,"0"),A2)

but then the apostrophe forms part of the value, rather than being treated as a formatting character, so when you fix the values in column B you will still see it displayed. A formula returns a value, not a format.

Hope this helps.

Pete

Ragulduy, Thanks a lot, it has helped!!