Leading apostrophe

[ItalianPlatinum]

I have a very large data set. where data that leads with zero will also lead with an apostrophe example ('0781). i tried different ways to try to resolve. copy and paste values, text to columns, comma delimited. with no success. if i double click manually into the cell it yields the result I need. problem is the data set is 10,000 rows so i can't do that. Does anyone have a quick way to resolve? I am open for VBA or anything at this point

 

[Trevor G]

Have you considered find and replace. Find the apostrophe and replace with nothing.

 

[ItalianPlatinum]

Have you considered find and replace. Find the apostrophe and replace with nothing.

Yes but that will remove the leading zero.

 

[Trevor G]

Change the format of the cells to include a leading zero

"0"#

 

[ItalianPlatinum]

Change the format of the cells to include a leading zero

"0"#

What if i have some data that is '0022 or '0001?

 

[Trevor G]

Are the numbers always the same length? If Yes try this:

Select the cells and use Format Cells, select Custom and in the option type in the amount of zeros to represent your desired numbers.

So if the cells have a max of six numbers and some start with maybe 2 zeroes by using this format it should still keep the 2 zeroes.

 

[ItalianPlatinum]

Are the numbers always the same length? If Yes try this:

Select the cells and use Format Cells, select Custom and in the option type in the amount of zeros to represent your desired numbers.

So if the cells have a max of six numbers and some start with maybe 2 zeroes by using this format it should still keep the 2 zeroes.

so my characters will always be 4. then the digits will vary. but they will all be 4

 

[Trevor G]

Changing the custom format should give you what you want as described in #6
0000

 

[ItalianPlatinum]

Changing the custom format should give you what you want as described in #6
0000

so like this?

"0000"#

 

[Trevor G]

Just the zeros as indicated no speech Mark's or extras. Format custom

0000