VBA preceding single quote disappears after running macro

I found and modified a macro that trims and cleans all cells in a given dataset.

The problem is that I have values in a column like this: '00001 that after running the clean & trim formula, look like this: 1.

I tried first changing the cell value to {{{00001 and then change it back to '00001 after the clean & trim code has completed, but it doesn't fix the issue.

Below is my code:

Please Login or Register  to view this content.

I found a way based on "prefixCharacter", but my macro is still really slow. At least it works. Does anyone have a better solution than the following?

Please Login or Register  to view this content.

UPDATE: Nevermind - it's not working. I had an unclosed IF statement that fooled me..

Hello joeyslaptop,

If you simply need to display the number with the leading zeroes then you can format the cells to do so. The advantages are it is automatic and you do not have to convert a string back into a number.

1. Select the cells or the entire column to be formatted.
2. Right Click and select Format Cells... from the context menu.
3. Under Category:, Click on Custom
4. To the right of the Category: list, look for the Type: input box.
5. Select the text in it and delete it
6. Next, type in 00000